SQL to find difference of dates - Oracle

Question

I have a table:

table1

tran_id    user_id    start_date    end_date
1          100        01-04-2018    02-04-2018
2          100        14-06-2018    14-06-2018
4          100        12-06-2018    12-06-2018
7          101        05-01-2018    05-01-2018
9          101        08-01-2018    08-01-2018
3          101        03-01-2018    03-01-2018

Date format is DD-MM-YYYY

I need to find the day difference ordered by user_id and start_date. The formula to find day difference is to subtract end_date of the prior record and start_date of the next.

Here is the expected output:

tran_id    user_id    start_date    end_date       day_diff
1          100        01-04-2018    02-04-2018     71
4          100        12-06-2018    12-06-2018     2
2          100        14-06-2018    14-06-2018     0

3          101        03-01-2018    03-01-2018     2
7          101        05-01-2018    05-01-2018     3
9          101        08-01-2018    08-01-2018     0

How to get this in an SQL query?

"Date format is `DD-MM-YYYY`" - do you mean columns type is not `DATE` ? Why? — Serg, Jul 08 '19 at 12:44
It is date. The format is DD-MM-YYYY in the question I meant. — dang, Jul 08 '19 at 12:45

score 1 · Accepted Answer · answered Jul 08 '19 at 12:49

Use function lead() to find next value, then substract dates:

select tran_id, user_id, start_date, end_date, nvl(nsd - start_date, 0) diff
  from (
    select t.*, lead(start_date) over (partition by user_id 
                                       order by start_date) nsd 
      from table1 t)

^demo

score 1 · Answer 2 · answered Jul 08 '19 at 13:12

You can use lead() for this. Because lead() takes three arguments, you don't need a subquery or to deal with null values:

select t.*,
       (lead(start_date, 1, start_date) over (partition by user_id order by start_date) -
        start_date
       ) as diff
from t;

score 0 · Answer 3 · answered Jul 08 '19 at 12:48

0

I think this: DATEDIFF function in Oracle in combination with the LAG function should help you out.

answered Jul 08 '19 at 12:48

Andreas Rolén

508
5
15

score 0 · Answer 4 · answered Jul 08 '19 at 13:59

A minor correction to the answer given by Ponder Stibbons. Difference highlighted in Bold.

select tran_id, user_id, start_date, end_date, nvl(nsd - end_date, 0) diff from ( select t.*, lead(start_date) over (partition by user_id order by start_date) nsd from date_test t);

Regards Akash

SQL to find difference of dates - Oracle

4 Answers4