Find date difference in Oracle SQL based on condition

I have a table:

table1

tran_id    user_id    start_date    end_date
1          100        01-06-2018    18-06-2018
2          100        14-06-2018    14-06-2018
4          100        19-07-2018    19-07-2018
7          101        05-01-2018    06-01-2018
9          101        08-01-2018    08-01-2018
3          101        03-01-2018    03-01-2018

DEMO – Link

Here is the logic:

I need to find the day difference between two trans_id for a member sorted by start_date where there is no overlapping start_date and end_date.

We need to check the max end_date for a user processing one record at a time.

The logic would be:

For a member:

• sort all records by user_id and start_date
• trans_id = 1, end_date = 18-06-2018 , set max_end_date = 18-06-2018
• trans_id = 2, end_date = 14-06-2018, end_date < max_end_date, move forward
• trans_id = 3, end_date = 19-07-2018, end_date > max_end_date, add a record in output where
  • transidfrom = 1 (as this is the record with max_end_date)
  • transidto = 4 (as this is the record where end_date > max_end_date)
  • transidfrom_end_date = 18-06-2018, pick the end_date of trans_id of transidfrom
  • transidto_start_date = 19-07-2018, pick the start_date of trans_id of transidto
  • datediff = transidto_start_date – transidfrom_end_date

The output would be as follows:

table2

my_id    transidfrom    transidto    transidfrom_end_date    transidto_start_date   datediff
1        1              4            18-06-2018              19-07-2018             31
2        3              7            03-01-2018              05-01-2018             2
3        7              9            06-01-2018              08-01-2018             2

Is there a way to do this in Oracle SQL in 1 query?

Answer

If I have understood your requirement correctly, then maybe this will do it:

FSITJA@db01 2019-07-08 12:08:59> with table1(tran_id, user_id, start_date, end_date) as (
  2      select 1, 100, date '2018-06-01', date '2018-06-18' from dual union all
  3      select 2, 100, date '2018-06-14', date '2018-06-14' from dual union all
  4      select 4, 100, date '2018-07-19', date '2018-07-19' from dual union all
  5      select 7, 101, date '2018-01-05', date '2018-01-06' from dual union all
  6      select 9, 101, date '2018-01-08', date '2018-01-08' from dual union all
  7      select 3, 101, date '2018-01-03', date '2018-01-03' from dual )
  8  select rownum        as my_id,
  9         tran_id       as transidfrom,
 10         next_tran_id  as transidto,
 11         end_date      as transidfrom_end_date,
 12         next_end_date as transidto_start_date,
 13         datediff
 14    from (select tran_id,
 15                 user_id,
 16                 start_date,
 17                 end_date,
 18                 lead(tran_id) over (partition by user_id order by end_date) next_tran_id,
 19                 lead(start_date) over (partition by user_id order by end_date) next_end_date,
 20                 lead(start_date) over (partition by user_id order by end_date) - end_date datediff
 21            from table1)
 22   where datediff > 0;

     MY_ID TRANSIDFROM  TRANSIDTO TRANSIDFROM_END_DAT TRANSIDTO_START_DAT   DATEDIFF
---------- ----------- ---------- ------------------- ------------------- ----------
         1           1          4 2018-06-18 00:00:00 2018-07-19 00:00:00         31
         2           3          7 2018-01-03 00:00:00 2018-01-05 00:00:00          2
         3           7          9 2018-01-06 00:00:00 2018-01-08 00:00:00          2

3 rows selected.

SQL Fiddle example