how to find which rows are being overlap while finding overlap b/w the dates

Question

How can we find the between the overlap lap b/w the dates . overlap means when start date and end date are within same range for below example row 1 has no over lap. Row 2to 5 can be considered as one set of over lap as there start date and end are over lap with themselves Row 6 & 7 can be considered as one set of over lap for eg. row 6 & 7 --> start date of row 7 is in same range with respect to end date of row 6 so it becomes an overlap

Once overlap is found then and need to find out min(start date) and max(end date) and want to assign a unique id to each overlap and in the S.NO column should show which rows are overlapped.

Below is the I/p and O/p

MT0 · Accepted Answer · 2020-11-11T11:56:38.877

Based on this answer (which contains a detailed explanation of the query), you can change the final part to generate groups of overlapping rows and then aggregate using LISTAGG to get the delimited sno values:

SELECT id,
       MIN( dt ) AS start_date,
       MAX( dt ) AS end_date,
       LISTAGG( CASE value WHEN 1 THEN sno END, ',' )
         WITHIN GROUP ( ORDER BY dt ) AS snos
FROM   (
  SELECT sno,
         id,
         dt,
         value,
         SUM( start_end ) OVER ( ORDER BY dt ASC, value DESC )
           AS grp
  FROM   (
    SELECT sno,
           id,
           dt,
           value,
           CASE SUM( value ) OVER ( ORDER BY dt ASC, value DESC, ROWNUM ) * value
           WHEN 1 THEN 1
           WHEN 0 THEN 0
           ELSE NULL
           END AS start_end
    FROM   table_name
    UNPIVOT ( dt FOR value IN ( start_date AS 1, end_date AS -1 ) )
  )
)
GROUP BY id, grp;

Which, for the sample data:

CREATE TABLE table_name ( sno, id, start_date, end_date ) AS
SELECT 1, 1, DATE '2020-10-11', DATE '2020-10-11' FROM DUAL UNION ALL
SELECT 2, 1, DATE '2020-11-04', DATE '2020-12-11' FROM DUAL UNION ALL
SELECT 3, 1, DATE '2020-11-05', DATE '2020-11-10' FROM DUAL UNION ALL
SELECT 4, 1, DATE '2020-11-06', DATE '2020-11-10' FROM DUAL UNION ALL
SELECT 5, 1, DATE '2020-11-20', DATE '2020-12-20' FROM DUAL UNION ALL
SELECT 6, 1, DATE '2021-01-01', DATE '2021-01-20' FROM DUAL UNION ALL
SELECT 7, 1, DATE '2021-01-01', DATE '2021-03-25' FROM DUAL;

Outputs:

ID | START_DATE          | END_DATE            | SNOS   
-: | :------------------ | :------------------ | :------
 1 | 2020-10-11 00:00:00 | 2020-10-11 00:00:00 | 1      
 1 | 2020-11-04 00:00:00 | 2020-12-20 00:00:00 | 2,3,4,5
 1 | 2021-01-01 00:00:00 | 2021-03-25 00:00:00 | 6,7

db<>fiddle here

From Oracle 12c you can use MATCH_RECOGNIZE for a much simpler query:

SELECT id,
       MIN( start_date ) AS start_date,
       MAX( end_date )   AS end_date,
       LISTAGG( sno, ',' ) WITHIN GROUP ( ORDER BY start_date ASC, END_DATE ASC )
         AS snos
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY start_date ASC, end_date ASC
  MEASURES  MATCH_NUMBER() AS grp
  ALL ROWS PER MATCH
  PATTERN ( OVERLAPPING_DATES* LAST_DATE )
  DEFINE OVERLAPPING_DATES AS NEXT( start_date ) <= MAX( end_date )
)
GROUP BY id, grp

db<>fiddle here

thanks MTO , but unpivot will be little bit costlier operation , i am able to achieve this result using analytic function except s.no as i dont have any variable like START_END as in your case . can there be any other way to achive S.no with out using START_END variable — Yatindra Kumar Janghel, Nov 11 '20 at 12:18

Gordon Linoff · Answer 2 · 2020-11-11T12:07:25.283

This is a type of gap-and-islands problem. You can solve this using a cumulative max to determine if there is an overlap. Then a cumulative sum and aggregation do what you want:

select id, min(start_date), max(end_date),
       listagg(s_no, ',') within group (order by s_no) as s_nos
from (select t.*,
             sum(case when prev_end_date >= start_date then 0 else 1 end) over (partition by id order by s_no) as grp
      from (select t.*,
                   max(end_date) over (
                       partition by id
                       order by s_no
                       rows between unbounded preceding and 1 preceding
                      ) as prev_end_date
            from t
           ) t
     ) t
group by id, grp;

Here is a db<>fiddle.

@Tejash . . . It does now. – Gordon Linoff Nov 11 '20 at 12:07 — Gordon Linoff, Nov 11 '20 at 12:07

how to find which rows are being overlap while finding overlap b/w the dates

2 Answers2