CODEWITHSUNDEEP
sqlsql-server-2012
Clem_Fandango
Clem_Fandango

Reputation: 364

Calculating time difference between 2 rows

I have a table which has information on races that have taken place, it holds participants who took part, where they finished in the race and what time they finished. I would like to add a time difference column which shows how far behind each participant was behind the winner. 

Race ID   Finish place     Time         Name
   1             1        00:00:10      Matt
   1             2        00:00:11      Mick
   1             3        00:00:17      Shaun
   2             1        00:00:13      Claire
   2             2        00:00:15      Helen

What I would like to See

Race ID   Finish place     Time       Time Dif   Name
   1             1        00:00:10               Matt
   1             2        00:00:11    00:00:01   Mick
   1             3        00:00:17    00:00:07   Shaun
   2             1        00:00:13               Claire
   2             2        00:00:15    00:00:02   Helen

I have seen similar questions asked but I was unable to relate it to my problem.
My initial idea was to have a number of derived tables which filtered out by finish place but there could be more than 10 racers so things would start to get messy. I'm using Management Studio 2012

Upvotes: 0

Views: 73

Answers (3)

Burak
Burak

Reputation: 196

You can use window functions. MIN([time]) OVER (PARTITION BY race_id ORDER BY finish_place) gives first row's time value in the same race. DATEDIFF(SECOND, (MIN([time]) OVER (PARTITION BY race_id ORDER BY finish_place)), time) gives the difference.

Upvotes: 0

Adam Yan
Adam Yan

Reputation: 502

Using http://www.convertcsv.com/csv-to-sql.htm to build example data:

DROP TABLE IF EXISTS mytable

CREATE TABLE mytable(
   Race_ID      INTEGER 
  ,Finish_place INTEGER 
  ,Time         VARCHAR(30)
  ,Name         VARCHAR(30)
);
INSERT INTO mytable(Race_ID,Finish_place,Time,Name) VALUES (1, 1,'00:00:10','Matt');
INSERT INTO mytable(Race_ID,Finish_place,Time,Name) VALUES (1, 2,'00:00:11','Mick');
INSERT INTO mytable(Race_ID,Finish_place,Time,Name) VALUES (1, 3,'00:00:17','Shaun');
INSERT INTO mytable(Race_ID,Finish_place,Time,Name) VALUES (2, 1,'00:00:13','Claire');
INSERT INTO mytable(Race_ID,Finish_place,Time,Name) VALUES (2, 2,'00:00:15','Helen');

A CTE with only first finshed places would be easier to understand. 

WITH CTE_FIRST
AS (
    SELECT
        M.Race_ID
       ,M.Finish_place
       ,M.Time
       ,M.Name
    FROM mytable M
    WHERE M.Finish_place = 1
)
SELECT
        M.Race_ID
       ,M.Finish_place
       ,M.Time
       ,CASE
            WHEN m.Finish_place = 1
                THEN NULL
            ELSE CONVERT(VARCHAR, DATEADD(ss, DATEDIFF(SECOND, c.Time, M.Time), 0), 108)
        END AS [Time Dif]
       ,M.Name
    FROM mytable M
    INNER JOIN CTE_FIRST c
        ON M.Race_ID = c.Race_ID

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use min() as a window function:

select t.*,
       (case when time <> min_time then time - min_time
        end) as diff
from (select t.*, min(t.time) over (partition by t.race_id) as min_time
      from t
     ) t

I would be more inclined to express this as seconds:

       (case when time <> min_time then datediff(second, min_time, time)
        end) as diff

Upvotes: 2

Related Questions