CODEWITHSUNDEEP
sqlt-sql
user2181948
user2181948

Reputation: 1898

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

Upvotes: 1

Views: 2502

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

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.

Upvotes: 7

Related Questions