February 13, 2012 at 11:21 pm
Hi,
I'm working on a results processing procedure that takes raw race results data (race number, elapsed time) and turns it into user-friendly nice-ness by incorporating additional information and, importantly for the purposes of this post, adding a ranking, or finishing position. In the past, this has been easy, because each event was one race made up of multiple legs. Easy stuff. Now however, we have multiple races for the same event that have no bearing on each other. So there's no 'overall' winner, just a bunch of individual race winners.
So what I need to do is pass a list of race names to the procedure, then add a ranking to the competitors table (tblResults) that resets for each race given in the list. I'm passing the race names as a comma seperated list which I then convert into a temporary table called @raceNames.
Below is the code I hacked up before the last event to get this happening quickly. Basically I just repeated this code for each different race name; dodgy, I know. What I need to remove is the line that hard codes the race name (AND LOWER(vchRaceName) like '%Race1Name%'), and somehow get it to use the values in the temporary @raceNames table.
CREATE TABLE #OverallTime (iCounter INTEGER, iRaceNumber INTEGER)
SET @i = 0
INSERT #OverallTime
(iCounter,
iRaceNumber)
SELECT 1,
iRaceNumber
FROM dbo.tblResults
WHERE iEventId = @piEventId
AND iYear = @piYear
AND LOWER(vchRaceName) like '%Race1Name%'
ORDER BY dtRaceTime
UPDATE #OverallTime
SET iCounter = @i,
@i = @i + 1
UPDATE tblResults
SET iOverallPlacing = iCounter
From tblResults R
JOIN
#OverallTime OT ON R.iRaceNumber = OT.iRaceNumber
WHERE R.iYear = @piYear
AND R.iEventId = @piEventId
Sorry for the long post, but I would love some help on this.
Many Thanks
Jason
February 14, 2012 at 1:43 am
Hi Jason ,
Would you be able to post some sample data (with DDL scripts) - might be able to help a little easier if we could see the tables your working with 🙂
Also, just a thought, instead of passing in a comma separated list, have you thought about the use of Table-Valued Parameters?
February 14, 2012 at 1:47 am
Sorry but I didn’t understand what you need. I’ve also noticed that there are many readers to your question, but no answer yet. I suggest that you’ll repost your question, but try to make it more understandable. Pleas add a small scripts that demonstrates your problem. The script should include creation of tables and insert statements. Then explain the results that you expect to get. Notice that in my signature there is a link to a post of how to ask questions in a better way. Pleas read it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 14, 2012 at 4:24 pm
OK guys, sorry for the un-helpful post, this is my attempt to be better.
I have a table of Raw Results that looks like this:
CREATE TABLE tblRawResults {
iRaceNumber INTEGER,
dtNetTime DATETIME
}
Which I run through a stored prodecudure to turn into this:
CREATE TABLE tblResults {
iRaceNumber INTEGER,
iEventId INTEGER,
iYear INTEGER,
vchRaceName VARCHAR(50),
iOverallPosition INTEGER,
iGenderPosition INTEGER,
dtRaceTime DATETIME,
vchName VARCHAR(100),
vchGender VARCHAR(10)
}
ALTER TABLE tblResults
ADD CONSTRAINT pk_RaceResult PRIMARY KEY (iRaceNumber,iEventId,iYear)
Code below will populate tblResult directly instead of copying from external table (too many tables to post otherwise):
INSERT INTO tblResults
(iRaceNumber, iEventId, iYear, vchRaceName, dtRaceTime, vchName, vchGender)
SELECT '1',1,2012,'Race 1', '1900-01-01 00:23:29.000', 'Jenny', 'female' UNION ALL
SELECT '2',1,2012,'Race 1', '1900-01-01 00:27:29.000', 'Gary', 'male' UNION ALL
SELECT '3',1,2012,'Race 2', '1900-01-01 00:23:44.000', 'Phillip', 'male' UNION ALL
SELECT '4',1,2012,'Race 1', '1900-01-01 00:23:48.000', 'James', 'female' UNION ALL
SELECT '5',1,2012,'Race 2', '1900-01-01 00:27:15.000', 'Peter', 'male' UNION ALL
SELECT '6',1,2012,'Race 1', '1900-01-01 00:23:00.000', 'Matthew', 'male' UNION ALL
SELECT '7',1,2012,'Race 1', '1900-01-01 00:21:00.000', 'John', 'female' UNION ALL
SELECT '8',1,2012,'Race 2', '1900-01-01 00:20:29.000', 'Tyler', 'female'
All the additional data is read in based on the race number and matched in an external table with the event ID and year passed in to the stored procedure (see procedure and relevant code below):
CREATE PROCEDURE [dbo].prcProcessMultiRaceResults
@piEventId INTEGER,
@piYear INTEGER,
@pvchRaces VARCHAR(255),
@pvchErrText VARCHAR(255) OUTPUT
AS
SET NOCOUNT ON
DELETE tblResults
WHERE iEventId = @piEventId
AND iYear = @piYear
-- Copy Data from external table to populate tblResult
-- Omitted for brevity and because it's a simple insert
-- Create temp table to add race position (ranking) to each competitor based on overall race time
CREATE TABLE #OverallTime (iCounter INTEGER, iRaceNumber INTEGER)
SET @i = 0
INSERT #OverallTime
(iCounter,
iRaceNumber)
SELECT 1,
iRaceNumber
FROM dbo.tblResults
WHERE iEventId = @piEventId
AND iYear = @piYear
ORDER BY dtRaceTime
UPDATE #OverallTime
SET iCounter = @i,
@i = @i + 1
UPDATE tblResults
SET iOverallPlacing = iCounter
FROM tblResults R
JOIN
#OverallTime OT
ON R.iRaceNumber = OT.iRaceNumber
WHERE R.iYear = @piYear
AND R.iEventId = @piEventId
However, this needs to change, as there is now more than one race for each event, identified by vchRaceName in tblResults. So what I'll be doing is passing in a comma seperated list into the stored procedure, and will need to rank competitors based on race name, so rather than there being, say rankings of 1-200 for 200 competitors in one race, there will now be rankings of 1-50 for four groups of 50 competitors in four races.
As mentioned in my previous post, earlier I had repeated the ranking code for each race, with hard-coded race names. This is what I would like to change. I'd like to set the ranking for each race based on the race names passed (@pvchRaces) in so I can re-use the code for all events, rather than hard-coding the race names.
Hopefully this is clearer than my last post
February 14, 2012 at 10:31 pm
You can do it with just an update statement that works with the ranking functions (in your case dense_rank function and a CTE). Here is an example that is based on your code:
;With Positions as (
select iRaceNumber,iEventId,iYear, DENSE_RANK() over (PARTITION by iEventID, iYear, vchRaceName order by dtRaceTime) as iOverallPosition,
dense_rank() over (PARTITION by iEventID, iYear, vchRaceName, vchGender order by dtRaceTime) as iGenderPosition
from tblResults)
update tblResults
set tblResults.iOverallPosition = Positions.iOverallPosition,
tblResults.iGenderPosition = Positions.iGenderPosition
from tblResults inner join Positions on tblResults.iRaceNumber = Positions.iRaceNumber AND tblResults.iEventId = Positions.iEventId
and tblResults.iYear = Positions.iYear
If you don't know what are the ranking functions or what is CTE, you can read about it in BOL. If after your read about, you still have questions, feel free to post some more questions.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply