Getting date difference between consecutive rows in the same group

Question

I have a database with the following data:

Group      ID      Time
1          1       16:00:00
1          2       16:02:00
1          3       16:03:00
2          4       16:09:00
2          5       16:10:00
2          6       16:14:00

I am trying to find the difference in times between the consecutive rows within each group. Using LAG() and DATEDIFF() (ie. https://stackoverflow.com/a/43055820), right now I have the following result set:

Group      ID       Difference
    1          1    NULL
    1          2    00:02:00
    1          3    00:01:00
    2          4    00:06:00
    2          5    00:01:00
    2          6    00:04:00

However I need the difference to reset when a new group is reached, as in below. Can anyone advise?

Group      ID       Difference
    1          1    NULL
    1          2    00:02:00
    1          3    00:01:00
    2          4    NULL
    2          5    00:01:00
    2          6    00:04:00

Edit your question and include your query. – Gordon Linoff Aug 17 '17 at 21:57 — Gordon Linoff, Aug 17 '17 at 21:57

score 6 · Accepted Answer · answered Aug 17 '17 at 21:58

The code would look something like:

select t.*,
       datediff(second, lag(time) over (partition by group order by id), time)
from t;

This returns the difference as a number of seconds, but you seem to know how to convert that to a time representation. You also seem to know that group is not acceptable as a column name, because it is a SQL keyword.

Based on the question, you have put group in the order by clause of the lag(), not the partition by.

Getting date difference between consecutive rows in the same group

1 Answers1

Linked