I Need A Quick Way To Return Place/rank/postion Of A Record?
Nov 14, 2007
I run an ASP.NET website with a monthly contest. Members can enter 1 script for each contest. Scripts can then be voted on.
There are three relevant SQL tables:
Contest with the field: ContestID
Script with the fields: ContestID, ScriptID, and Title
ScriptComment with the fields: ScriptID and Score
Scripts are grouped by contest and come in place (first, second, third, etc.) based on the average of all the scores they received.
I need a QUICK stored procedure that will accept ScriptID as a parameter and return back the place that script came in, in that particular contest.
Currently, I get this information by creating a virtual table and populating it with joined data from Script and ScriptComment. I order the table by average Score desc. I then use a select statement against that table using the ScriptID and return back the row number. This works, but it is very slow. There are times where I might need to list 300 scripts and I need to do this lookup for each script to display what place they came in.
I need a much faster way to get the place of any script in a contest. I think the Virtual Table is killing the speed. I have been trying to find a solution using a view or the new SQL 2005 command Row_Number(), but I have been unsuccessful.
If anyone has a speedy solution, I would love to hear it.
Thanks a lot,
Chris Messineo
View 3 Replies
ADVERTISEMENT
Nov 23, 2007
Hi all,
I don't know how to explain what I wanna do in english so here's a concrete example:
I have this data
Item - Qty
IT1 - 2
IT2 - 2
IT1 - 4
IT1 - 5
IT2 - 2
And I wanna do something like this
Item - Rank - Qty
IT1 - 1 - 2
IT1 - 2 - 4
IT1 - 3 - 5
IT2 - 1 - 2
IT2 - 2 - 2
So basically I want to assing a rank (on the fly) in a SELECT statement
Thanks in advance
Or Tho
View 10 Replies
View Related
Apr 16, 2014
I need to get the previous price for all my PROMOTION records but not when the previous record is a type PROMOTION also it needs to keep going back to get the price.
I have created a table with RANK in which works OK to get previous price for all but how can I say if previous price is type PROMOTION go to next previous prices...
Bets way to show an example is with a jpeg image I have but having trouble inserting into this message...
SELECT a.[StartPrice]
,a.[ProductID]
,a.[Colour]
,ISNULL(b.[Price],a.[Price]) AS [Price Before]
,a.[Price] AS [Promotion Price]
[Code] ...
Table
PriceCodeStartPriceEndPriceProductIDColourPriceRank
RETAIL21-Oct-1324-Dec-13Bike15BLUE39.001
PROMOTION29-Nov-1301-Dec-13Bike15BLUE31.202
PROMOTION12-Dec-1323-Dec-13Bike15BLUE31.203
MARKDOWN25-Dec-1314-Jan-14Bike15BLUE31.204
[Code] ....
Want I'm trying to do select PriceCode PROMOTION and get previous price:
Get previous price
PROMOTION29-Nov-1301-Dec-13Wheel1BLACK31.2039.00
But in this example it picks up the PROMOTION before and I need to ignore this and get rank 1 price for both
PROMOTION29-Nov-1301-Dec-13Bike15BLUE31.2039.00
PROMOTION12-Dec-1323-Dec-13Bike15BLUE31.2039.00
View 8 Replies
View Related
Oct 15, 2013
I have a table called Register that has the following in it
Policy_number, Policy_date_time, Portfolio_set, Status..The rule for the table is that the last record for each portfolio_set for a policy the status needs to be 'A' but there have been instances that the last record status is 'I'
I need to identify the record that is out of place..In the example below record number 2.
example
policy_number Policy_date_time Portfolio_set, status
12345 1/1/2011 1 I
12345 1/2/2011 1 A
12345 1/3/2011 1 I
12345 1/4/2011 1 I
I need to identify that the second record is in the wrong place...
View 4 Replies
View Related
May 27, 2006
i have a table name is HH tableit has two columns 'hhno' and hhname'HH tabelehhno hhname100 suresh101 baba103 rami want to insert a one record(102 , chandra) in HH table between(101,baba) and( 103 ,ram).how can i insert them please help ,me thanks
View 10 Replies
View Related
Jan 31, 2006
I've been looking for examples online to write a SPROC to get some data. Here are the tables.
Album_Category
AlbumCategoryID (PK, int, not null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
Albums
AlbumID (PK, int, not null)
AlbumCategoryID (int, null)
Caption (nvarchar(max), not null)
IsPublic (bit, not null)
I need to return:
-[Album_Category].[AlbumCategoryID]
-[Album_Category].[Caption]
-[Albums].[Single AlubmID for each AlbumCategoryID]
-[Count of Albums in each AlbumCategory]
I hope I was fairly clear in what I'm trying to do. Any tips or help would be appreciated. Thanks.
View 3 Replies
View Related
Feb 25, 2007
I have a directory of user information. What I would like to do isallow someone to search for person X and then return not only theinformation for person X, but also the information for the next 15people following person X sorted alphabetically by lastname.So if someone searched for the lastname = "Samson", it would return:Samson, JohnSaxton, GregScott, HeatherSears, Rebecca.... (15 names following "Samson) ...How do you in SQL return a record set of X records starting atparticular record (e.g. lastname = "Smith)?Thanks in advance.
View 4 Replies
View Related
Jul 20, 2007
Is there a place where i can find events that takes place in the sql server? Like adding data to a database or something like that....
Regards
Karen
View 4 Replies
View Related
Apr 1, 2008
Is there a SQL command that returns every other result or every 3rd result. The reason being is i am using the data to plot a graph with many thousands of points, it would be useful if not every result was return. I.e. too much data to fit within the screens resolution.
Regards Dan Walmsley
View 4 Replies
View Related
Mar 24, 2008
Is there a t-sql shortcut/ quick way to return the full max record.
So basically if I had:
prog_area glh description
HS 200 health and social care
EN 300 engineering
HS 400 health care
EN 250 engineering and construction
so grouping by prog_area and taking max glh would return appropriate descriptions like:
HS 400 health care
EN 300 engineering
This is quite a simple example and I would be using it on much larger datasets. I know how to do it by doing a join to itself with max glh but it seems to me there should be an easier way to return the appropriate full record set. Hopefully somethin fast in t-sql
View 2 Replies
View Related
Jul 20, 2005
i need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/
View 6 Replies
View Related
Feb 26, 2008
Hello,
The following query:
SELECT submitRep1 AS Rep, tc, COUNT(tc)AS TCCount
FROM tbl_CYProcessedSales
WHERE
tc NOT LIKE 'T%'
AND
tc NOT LIKE 'R%'
AND ISNUMERIC(TC) = 0
AND NOT submitrep1 = ''
AND Submitrep1 = 'along'
GROUP BY submitrep1, tc
Returns a result like this:
ALONG KL 65
ALONG KP 35
How can I return the one record that contains the MAX(TCCount)?
Thank you for your help!
CSDunn
View 10 Replies
View Related
Oct 21, 2013
I am wanting to run a SQL statement whereby i return the ID of any employee's Director.
The database for employees has a reports to field which enables me to see the hierarchy of managers above any employee.
There is also a IsDirector flag that indicates a director.
So essentially i want to run sql that would return the first instance of a director in the hierarchy above any employee.
eg if A reports to B and B reports to C (who is a director) then it returns C.
I basically want the script to run until a director is found.
how would i do this?
View 5 Replies
View Related
Jan 19, 2007
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
View 4 Replies
View Related
Jun 15, 2007
Hey everybody!
How do i insert a carriage return at the end of an record that's being sent to a flat file? Currently, I get one long string, and would like for SSIS to put carriage returns at the end of each line.
Any ideas?
Thanks!
Jim Work
View 3 Replies
View Related
Sep 3, 2007
Given the Patients and PatientVisits tables as per below, how do I obtain the most recent (latest) Weight and Height for each patient as per http://www.hazzsoftwaresolutions.net/selectStatement.htm
The result of the query should only return 3 rows/records,not 5. Thank you. Greg
Code Snippet
select p.ID, p.FirstName,p.LastName,DATEDIFF(year, p.DOB, getdate()) AS age
,pv.WeightPounds, pv.HeightInches
from Patients as p
inner join PatientVisits as pv
ON p.ID = PV.PatientID
order by pv.VisitDate desc
INSERT INTO Patients (ID, FirstName,LastName,DOB)
select '1234-12', 'Joe','Smith','3/1/1960'
union
select '5432-30','Bob','Jones','3/1/1960'
union
select '3232-22','Paul','White','5/12/1982'
INSERT INTO PatientVisits (PatientID, VisitDate,WeightPounds,HeightInches)
select '1234-12', '10/11/2001','180','68.5'
union
select '1234-12', '2/1/2003','185','68.7'
union
select '5432-30','11/6/2000','155','63.0'
union
select '5432-30','5/12/2001','165','63.0'
union
select '5432-30','4/5/2000','164','63.5'
union
select '3232-22','1/17/2002','220','75.0'
CREATE TABLE [dbo].[Patients](
[PID] [int] IDENTITY(1,1) NOT NULL,
[ID] [varchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED
CREATE TABLE [dbo].[PatientVisits](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PatientID] [nvarchar](50) NULL,
[VisitDate] [datetime] NULL,
[WeightPounds] [numeric](18, 0) NULL,
[HeightInches] [decimal](18, 0) NULL
) ON [PRIMARY]
View 11 Replies
View Related
Jan 24, 2006
For example:
Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location
I know MS Access you can use a "*" to retrieve all records when there is a parameter. What does SQL Sever query use to retrieve all record?
View 14 Replies
View Related
Dec 18, 2012
Using MSSQL 2008 R2
Given the following table
Code:
{ID, PropClass, OffSet, Amount}
{1, 1, 1, .30}
{2, 1, 2, .45}
{3, 1, 3, .50}
{4, 2, 1, .26}
{5, 2, 2, .15}
If I know the exact offset I can query easily enough using PropClass and the exact offset. But what if the offset is not included in the range for a given PropClass? How can I get a query to return the last valid record for a given PropClass from within a join?
For example, if my query contained PropClass = 1 and offset = 4, it should return the Amount of .50 from Record with ID 3
This is a query that I am trying to work on:
Code:
SELECT v.District, v.PropClass, YearAquired, SUM(cost * cnt), SUM(v.Cost * v.Cnt * t.Amount), SUM(v.Tax), COUNT(*)
FROM UPValue v
INNER JOIN UPMaster m on m.Year = v.year and m.Account = v.account
INNER JOIN UPTable T on t.PropClass = v.PropClass and t.Offset = v.Year - v.YearAquired
WHERE v.Year = 2012 and LeaseType = 2
group by v.District, v.PropClass, YearAquired
order by v.District, v.PropClass
Using <= will not work because that would return multiple records from UPTable when the offset is < the max offset.
View 4 Replies
View Related
Apr 12, 2015
I have a table with records like that.
Group | Value
Team 1 | 0
Team 1 | 0
Team 1 | 1
Team 1 | 1
Team 2 | 0
Team 2 | 0
Team 2 | 0
I want a script that return 0 if all the values of the group are 0 and return 1 if the records of the value is mixed with 0 and 1.
View 1 Replies
View Related
Jan 15, 2015
I want to return Order records which are one type and don't have the other type.
The issue is I have Orders with which has 2 distriubtion types .
Example
Order 12345 has Type S and Type X.
Order 67891 has Type S
I only want to return Order 67891 that are s Type and does not have type X
View 1 Replies
View Related
Feb 26, 2007
I'm trying to return the total records with my query, but I'm getting the following error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."
Here's my query:
set rsFind = conn.Execute ("Select Count(Incident_ID) as TotalCount, Incident_ID, ProblemDescriptionTrunc, Action_Summary, RootCause, Problem_Solution002, " _
& " AssignedTechnician, DATEADD(s, dbo.TTS_Main.DateClosed, '1/1/1970') AS DateClosed, DATEADD(s, dbo.TTS_Main.Date_Opened, '1/1/1970') AS DateOpened, AssignedGroup From tts_main Where ProblemDescriptionTrunc LIKE '%" & prob & "%' And Last_Name LIKE '%" & l_name & "%' " _
& " AND AssignedTechnician LIKE '%" & assigned_tech & "%' And Incident_ID LIKE '%" & ticketnum & "%' AND assignedgroup LIKE '%" & assigned_group & "%' " _
& " Order By DateClosed DESC ")
<%response.write rsfind("TotalCount")%>
Thanks for any help!
Dale
View 10 Replies
View Related
May 3, 2007
I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic
and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4
I need to get back something like this
TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4
that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client
this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this
TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray
but I am not sure how this idea could be implemented using the capabilities of SQL.
my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.
any ideas in how to make this better ?
View 8 Replies
View Related
Apr 28, 2015
I have been researching BOL and other online resources but cannot seem to get a definitive answer.
Current Output:
[MemberID][Category][Type]
12345ABCtest
12345XYZtest
12777ABCtest
12888FGDtest
Desired Output:
[MemberID][Category][Type]
12345ABCtest
12777ABCtest
12888FGDtest
Query:
SELECT m.MemberID,
vw.Category,
vw.Type,
FROM dbo.TestVW vw JOIN
dbo.TestMember m ON vw.MemberKey = m.MemberKey
WHERE vw.Type = 'test'
GROUP BY m.MemberID,
[Code] ...
but cannot seem to be able to return one record with its corresponding value criteria.
View 21 Replies
View Related
May 7, 2012
I have 3 tables I'm trying to join:
Table 1: Contains ID, Name, Description
Table 2: Contains ID, Keyword
Table 3: Is lookup table for tables 1 & 2
I need a query to return a table having columns of:
Name, Description, Keyword for every record ID in Table 1
The issue is that table 2 contains multiple keywords for each key ID and my query returns an error: Subquery returned more than 1 value.
When I run this query using a specific key, it returns the correct information the way I want it:
DECLARE @ServiceID VARCHAR(10)
SET @ServiceID = '35'
DECLARE @Name VARCHAR(8000) DECLARE @Desc VARCHAR(8000) DECLARE @Keywords VARCHAR(8000)
SELECT @Name = [DefService].[Name],@Desc = [DefService].[Description],@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name] FROM [DefService] INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] WHERE [DefService].[ServiceID] = @ServiceID PRINT @Name + ' | ' + @Desc + ' | ' + @Keywords
SELECT @Name AS Name,@Desc AS Description,@Keywords AS Keywords
Results:
NameDescriptionKeywords
GoToMyPC - Account RequestRequest a Citrix GoToMyPC account.GTMPC, GoTo, application, software, install, Installation, applications
When I run the same query without a specific key it fails. The results only return a single row containing Name, Description and then ALL keywords for every key ID...very odd behavior.
BTW, I need to do this in a single SQL query and not a stored proc or other method.
View 10 Replies
View Related
Nov 26, 2005
We're using ASPUpload as a tool to upload files to our server and savethe details to SQLServer. However, I have an application where I needto return the pkID of the just saved file. I'm assuming that I coulduse the @@Identity command but cannot get this to function.Has anyone used this command with ASPUpload with an success, or anyother methods that could be used?Thanks.
View 8 Replies
View Related
Jan 4, 2007
Hi everyone,
I've created a DTS package runs on every day and night, but now my boss was asking if I can insert an exception code to check the view file.
So.. I need help from you guys, cause I don't know How.
This is my DTS description.
My DB will generate a view called "Calls to Add", then it will run the Transform Data Task and insert into a txt file. once it finished, it will run the Batch file. that is it.
Now My boss wants me to add a checking code between "View to Txt" procedure. If the view has no record inside, than the DTS package should stop and not run.
BUT How??? Can someone please help?? Thanks
View 10 Replies
View Related
Jul 18, 2007
Dear all,
I am using C# , asp.net and sql server 2005.
Let me explain the situation.
I have written procedure to insert data into the table and return last inserted value by @@identity variable. Now my question is how do I execute this process so that I can
Get last inserted variable values
Please help
thanks
View 3 Replies
View Related
Apr 16, 2014
I'm using a Merge statement to update/insert values into a table. The Source is not a table, but the parameters from a Powershell script. I am not using the Primary Key to match on, but rather the Computer Name (FullComputerName).
I am looking on how-to return the Primary Key (ComputerPKID) of an updated record as "chained" scripts will require a Primary Key, new or used.As an aside: the code below does return the newly generated Primary Key of an Inserted record.
CREATE PROCEDURE [dbo].[usp_ComputerInformation_UPSERT](
@FullComputerName varChar(50) = NULL
,@ComputerDescription varChar(255) = NULL
,@ComputerSystemType varChar(128) = NULL
,@ComputerManufacturer varChar(128) = NULL
[code]....
View 4 Replies
View Related
Nov 26, 2013
I have sql code that returns the correct number of record when run without an aggregate function like count(myfield) and group by myfield. It always returns 86 row which is correct when Select DISTINCT is used. As, expected when DISTINCT is not used I get double the number if rows or 172. But when I count(myfield) and group by myfield the count is 172 and not 86. The strangest thing about this is that when I am grouping a set of items
Group 1
Group 2
Group 3
The other group sum up correctly while others don't. What can explain this? Here is the code.
Select DISTINCT ws.p4Districtnumber, ws.cycle, ws.worksetid, count(msi.MeterSessionInputKey) as ASND
from fcs.dbo.WorkSet as ws
left outer join fcs.dbo.WorkAssignment as wa
on ws.WorkSetID = wa.WorkSetID
left outer join fcs.dbo.MeterSessionInput as msi
on wa.worksetkey = msi.worksetkey
[code]....
View 3 Replies
View Related
Aug 7, 2015
I have a single complex query.
SELECT
Col1, -- Header,
Col2, -- Header,
Col3, -- Detail
Col4, -- Detail
Col5, -- Detail
FROM
TableName;
The query repeats the Header row value for all children associated with the header.I need the output of the query in XML format such that..For every Header element in the XML, all its children should come under that header element//I am using -
SELECT
Cols
FROM
Table Names
FOR XML PATH ('Header'), root('root') , ELEMENTS XSINIL
This still repeats the header for each detail (in the XML) , but I need all children for a header under it.I basically want my output in this format -
<Header >
<detail 1>
</detail 1>
<Detail 2>
</Detail 2>
<detail 3>
</detail 3>
</Header>
View 2 Replies
View Related
Oct 2, 2007
I need to call the stored procedure below. Basically what I need to know is if the query returns a record?
Note I would be happy if I could just return the number Zero if no records are returned. Can someone please help me out here?Here is my query so far in SQL Server. I just don't know how to return a value based upon the result of the records returned from the query.
GOCREATE PROCEDURE [dbo].[GetNameStatus]( @CountryId decimal, @NameId decimal, @DescriptionId decimal)AS SELECT Name.Active FROM Name INNER JOIN NameDescription ON Name.NameId = NameDescription.NameId WHERE Name.CountryId=@CountryId AND Name.NameId=@NameId AND NameDescription.DescriptionId=@DescriptionId AND Name.Active='Y'
View 3 Replies
View Related
Jun 2, 2004
I have two tables:
1. tblProducts
columns: ProductID, ProductName
2. tblProductCodes
columns: ProductID, CustomerID, ProductCode
The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.
I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.
I tried:
SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,
dbo.tblProducts.ProductName,
dbo.tblProducts.ProductID
FROM dbo.tblProducts LEFT OUTER JOIN
dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID
WHERE dbo.tblProductCodes.CustomerID = 2
But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.
Any help is highly appreciated.
View 4 Replies
View Related
Oct 16, 2004
I have table Products and Orders that has the following columns:
table Products: ProductID, ProductName
table Orders: OrderID, ProductID, OrderDate, Quantity, Price
The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.
I tried the following select statement:
select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'
Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :
ProductName / Quantity / Price
Product-A 5 1.89
Product-B 6 2.43
Product-C null null
Obviously, my sql statement won't work becaue the where clause will filter out Product-C.
Could anyone help me figure out how to modify my sql code to get the resultset I want?
Thanks in advance.
View 2 Replies
View Related
