CODEWITHSUNDEEP
sqldb2
Joshua Averbuch
Joshua Averbuch

Reputation: 97

Date Difference between rows for each id SQL

I have a query that produces data in the following format:

ID | DATE                       | STATUS
---+----------------------------+----------
93 | 2018-02-01 14:23:31.90123  | DRAFT
94 | 2018-02-01 14:23:32.44114  | DRAFT
94 | 2018-02-01 16:06:57.971881 | SUBMITTED
94 | 2018-02-01 16:07:25.940212 | REROUTE
94 | 2018-02-01 16:07:25.940212 | SUBMITTED
95 | 2018-02-01 14:24:51.749198 | DRAFT

I am trying to structure another query that will take this data and calculate the difference between each date. The ultimate goal is to figure out how long each ID is stuck in a specific STATUS. Does anyone have any ideas on how to go about doing this? The initial query already orders the data by ID and DATE.

Upvotes: 2

Views: 69

Answers (3)

Esperento57
Esperento57

Reputation: 17462

Other method if your system dont have lag option

select f1.*, f2.*
from yourtable f1
left outer join lateral
(
    select * from yourtable f0
    where f0.id=f1.id and rrn(f1)<>rrn(f0)
    order by f0.DATE desc 
    fetch first rows only                      

) f2 on 1=1

Upvotes: 0

jose_bacoy
jose_bacoy

Reputation: 12684

@JOSHUA; Here is the query and I test using ORACLE. Hope it helps. Thanks!

select ID,
DATETIME,
STATUS,
ROUND(24*60*60*(DATETIME - LAG(DATETIME) OVER (PARTITION BY ID ORDER BY DATETIME)),2) AS TIME_DIFF_SEC
FROM TABLE

Result:

ID  DATETIME                STATUS      TIME_DIFF_SEC
93  2018-02-01T14:23:31Z    DRAFT       (null)
94  2018-02-01T14:23:32Z    DRAFT       (null)
94  2018-02-01T16:06:57Z    SUBMITTED   6205
94  2018-02-01T16:07:25Z    REROUTE       28
94  2018-02-01T16:07:25Z    SUBMITTED      0
95  2018-02-01T14:24:51Z    DRAFT       (null)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Use lag():

select t.*, lag(datetime) over (partition by id order by datetime) as prev_datetime
from t;

Upvotes: 1

Related Questions