March 19, 2016 at 8:45 am
Hi
I have a table with some data, this table stores times from several points for each stage (it's a bike race with more than one stage). For each biker, in every stage i have times in 3 different points, (P1,P2 and END), each one of the them includes the previous time, so if the user in the P1 has 3 and in the P2 has 5, it took 2 from P1 to P2.
After each stage i need to get the Overall, the first one it's easy, just looking to stage = 1 and the point = 'END' and order by the time.
Now, how to get the overall after stage 2, and after stage 3 and so on?
Knowing that in some cases the athlete doesn't finish the stage and in others it doesn't start, so in this cases it doesn't have anytime in the end point.
So i need to get the data order, in the first place for the athletes that completed all the stages till the stage i'm getting the overall and by the minimum sum of the total time spent.
Some sample data:
DECLARE @TempTable TABLE(
Stage INT,
Num INT,
Point NVARCHAR(5),
TimeSpent INT
)
INSERT INTO @TempTable
SELECT 1,1,'P1',3
UNION ALL
SELECT 1,1,'P2',5
UNION ALL
SELECT 1,1,'END',10
UNION ALL
SELECT 1,2,'P1',4
UNION ALL
SELECT 1,2,'P2',6
UNION ALL
SELECT 1,2,'END',11
UNION ALL
SELECT 2,1,'P1',7
UNION ALL
SELECT 2,1,'P2',11
UNION ALL
SELECT 2,1,'END',15
UNION ALL
SELECT 2,2,'P1',5
UNION ALL
SELECT 2,2,'P2',12
-- Simple query for stage classification
SELECT Num,TimeSpent
FROM @TempTable
WHERE Stage = 1 AND Point = 'END'
ORDER BY TimeSpent
A second requirement, not so critical, it's getting a summary table, that in the columns i have the athlete and every stage and another column with the total time. To fill the table use the same approach, first the athletes that finished all the stages order by the minimum time spent, then the other ones order by the number of stages that they finished and then by the time they spent to finish that number of stages. Kind of a inverted triangle...
Sample:
Athlete | Stage 1 | Stage 2 | ... | Total
Name1 | 10 | 7 | .... | 17
Name2 | 07 | DNF | ... |7
...
Thanks
March 19, 2016 at 9:19 am
Sample:
Athlete | Stage 1 | Stage 2 | ... | Total
Name1 | 10 | 7 | .... | 17
Name2 | 07 | DNF | ... |7
for clarification....is the above your expected results from your test data?
if not can you please expected results.
thanks
...
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 19, 2016 at 9:27 am
No, the values were made up... sorry
With the test data, after stage 2, with a total of 4 stages, should be:
1 | 10 | 15 | - | - | 25
2 | 11 | DNF| - | - | 11
Thanks
March 19, 2016 at 9:36 am
rootfixxxer (3/19/2016)
No, the values were made up... sorryWith the test data, after stage 2, with a total of 4 stages, should be:
1 | 10 | 15 | - | - | 25
2 | 11 | DNF| - | - | 11
Thanks
sorry ....but can you explain how you get these results... I obviously need caffeine/alcohol 🙂
you say four stages....dont see test data for this?
edit....ok....get it now....apologies for being thick !!
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 19, 2016 at 9:58 am
The table can be consulted at anytime, in this case the user is looking to it at the end of second stage. So i have only values for the first two stages. ..
I have two requirements:
First, the overall with only the athletes that finalize all the stages.
Second, the summary table.
March 19, 2016 at 10:11 am
try this for starters
SELECT Num, MAX(CASE WHEN stage = 1 THEN timespent ELSE 0 END) AS s1,
MAX(CASE WHEN stage = 2 THEN timespent ELSE 0 END) AS s2,
SUM(timespent) as sofar
FROM @temptable
WHERE (Point = 'end')
GROUP BY Num
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 19, 2016 at 12:33 pm
Thanks
But that doesn't work weel, because the rows must be order by time sofar, and the number 2 will have less time because it haven't completed the second stage...
March 19, 2016 at 12:50 pm
rootfixxxer (3/19/2016)
ThanksBut that doesn't work weel, because the rows must be order by time sofar, and the number 2 will have less time because it haven't completed the second stage...
so what do you really want to see.....based on this
rootfixxxer (3/19/2016)
No, the values were made up... sorry
With the test data, after stage 2, with a total of 4 stages, should be:
1 | 10 | 15 | - | - | 25
2 | 11 | DNF| - | - | 11
the code gives you what I think you want (apart from 0 replacing 'DNF')
can you please provide more test data and expected results to represent why this the code isnt working.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 19, 2016 at 4:40 pm
Just add another athlete to the temp table:
UNION ALL
SELECT 1,3,'P1',3
UNION ALL
SELECT 1,3,'P2',4
UNION ALL
SELECT 1,3,'END',7
UNION ALL
SELECT 2,3,'P1',1
UNION ALL
SELECT 2,3,'P2',3
UNION ALL
SELECT 2,3,'END',9
Then using your code, with the order by
SELECT Num,
MAX(CASE WHEN stage = 1 THEN timespent ELSE 0 END) AS s1,
MAX(CASE WHEN stage = 2 THEN timespent ELSE 0 END) AS s2,
SUM(timespent) as sofar
FROM @temptable
WHERE (Point = 'end')
GROUP BY Num
ORDER BY SUM(timespent) ASC
You'll see that the athlete that shows first is the number 2, and that's wrong because it only finished the first stage.
The correct order should be, number 3 (total=16, stages completed 2), then number 1 (total=25, stages completed 2), then finally the number 2 (total=11, but only completed the first stage).
Thanks
March 20, 2016 at 4:38 am
rootfixxxer (3/19/2016)
Just add another athlete to the temp table:
UNION ALLSELECT 1,3,'P1',3
UNION ALL
SELECT 1,3,'P2',4
UNION ALL
SELECT 1,3,'END',7
UNION ALL
SELECT 2,3,'P1',1
UNION ALL
SELECT 2,3,'P2',3
UNION ALL
SELECT 2,3,'END',9
Then using your code, with the order by
SELECT Num,MAX(CASE WHEN stage = 1 THEN timespent ELSE 0 END) AS s1,
MAX(CASE WHEN stage = 2 THEN timespent ELSE 0 END) AS s2,
SUM(timespent) as sofar
FROM @temptable
WHERE (Point = 'end')
GROUP BY Num
ORDER BY SUM(timespent) ASC
You'll see that the athlete that shows first is the number 2, and that's wrong because it only finished the first stage.
The correct order should be, number 3 (total=16, stages completed 2), then number 1 (total=25, stages completed 2), then finally the number 2 (total=11, but only completed the first stage).
Thanks
ORDER BY COUNT(CASE WHEN Point = 'end' THEN 1 ELSE 0 END) DESC , SUM(timespent) ASC
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 20, 2016 at 6:15 am
Ups...
I'm getting dumber everyday... :crazy:
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply