Hi guys, I've been friendly tasked from a biker association. The task consist in simulating a race reporting (the race is in two weeks so we are a little bit short..) Do you know how the Tour de France works? First stage there is a winner and all the bikers behind so you have a situation like this:

Nibali 00.00

Contador 00.21

Wiggins 00.56

Valverde 01.22

..........

So contador has 21 seconds more than Nibali, Wiggins 56 sec, Valverde 1 minute and 22.

I should reproduce a simulation in order to test the reporting for an amateur race that is coming (no money, just helping these nice guys...). My point is that I already created the race as you can see if you test the DDL and the code below.

Anyway I am wondering how can I set the minimum time and how can I set the delay. It should be after stage 1 select biker, delay form min time where race <2 and after stage 2select biker, delay form min time aggregate (stage1 + stage2) where race <3, after stage 3 delay form min time aggregate (stage1 + stage2) where race <4.....but I am not sure.

 I am struggling to set up the query that can give me the leader and the delay...any advice? I am shattered today but I'd like to help the young passionate guys..

So we have already the big table, how would you run the reporting queries?

Thanks

Create table SimulationBiker (cd int identity, Biker varchar(30), nation varchar(30))
Insert into SimulationBiker  values('Nibali', 'Italy'), ('Froome','UK'),('Contador','Spain'),
('Quintana','Colombia'),('Van Garderen','USA'),('WIggins','UK'),
('Uran Uran','Colombia'), ('Aru','Italy'),('Genkins','Hollande'),
('Porte','UK'),('Contador','Spain'),('Pinot','France'),
('Rolland','France'),('Peraud','France'),('Valverde','Spain'),
('Mollema','Holland'),('Ten Dam','Spain'),('Bardet','France'),
('Mayka','Poland'),('Rodriquez','Spain')
create table Simulation (Biker int, Race int, TimeArrive datetime2)
Declare @stage int, @biker int, @dtt datetime2
set @stage=1
while @stage<20
begin
set @biker=1
while @biker<21
begin
select @dtt=dateadd(millisecond, cast(360000 * RAND() as int), convert(time, '00:00'))
insert into Simulation values (@biker, @stage,@dtt)
set @biker=@biker+1
end
set @stage=@stage+1
end
select * from simulation s inner join SimulationBiker b on s.Biker=b.cd

Done!!!

WITH CTE AS (
select  b.biker, nation, sum( DATEPART(SECOND, [timeARRIVE]) + 60 * 
              DATEPART(MINUTE, [timeARRIVE]) + 3600 * 
              DATEPART(HOUR, [timeARRIVE] ) 
            ) AS TOT_TIME from simulation s inner join SimulationBiker b on s.Biker=b.cd
where race<4  GROUP BY  b.biker, nation ),cte2 as (
SELECT 'a' as app, biker, nation, tot_time, DATEADD(ms, tot_time * 1000, 0) as realtime
from cte ),cte3 as (
select  'a' app2, MIN(realtime) as first_time from cte2 )
select biker, nation,  RIGHT(CONVERT(CHAR(8),DATEADD(second,DATEDIFF(second, first_time, realtime),0),108),5)
 from cte2 c2 inner join cte3 c3 on c2.app=c3.app2 order by 
DATEDIFF(second, first_time, realtime) 

One possibility would be to combine CTE and cte2 to get realtime in one step.  You can get rid of cte3 by using a windowed MIN function (that is using MIN with the OVER clause), and doing a little reformatting, you get

WITH CTE AS (
select  b.biker, nation, DATEADD(SECOND, SUM(DateDiff(SECOND, '19000101', [timeARRIVE])), '19000101') AS realtime 
 from simulation s 
 inner join SimulationBiker b on s.Biker=b.cd
 where race<4  
 GROUP BY  b.biker, nation )

select biker, nation,  RIGHT(CONVERT(CHAR(8),DATEADD(second,DATEDIFF(second, Min(realtime) Over(), realtime),0),108),5)
 from cte 
 order by DATEDIFF(second, Min(realtime) Over(), realtime) 

Since both your query and this one will produce the same result, which one is better may be mostly a matter of coding style. 

Tom