user2181948 user2181948 - 1 month ago 7
SQL Question

Getting date difference between consecutive rows in the same group

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

Answer Source

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.