Your Location is: Home > Sql

Finding overlapping and calculation of min and max dates with in overlap in oracle [duplicate]

From: Athens View: 2876 Yatindra Kumar Janghel 

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 .Below is the I/p and O/p


I/p enter image description here

o/p enter image description here

Best answer

You can do it simply and efficiently using MATCH_RECOGNIZE to perform a row-by-row comparison and aggregation:

SELECT id, start_date, end_date
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER     BY start_date
  MEASURES  FIRST(start_date) AS start_date,
            MAX(end_date) AS end_date
  PATTERN   ( overlapping_dates* last_date )
  DEFINE    overlapping_dates as MAX(end_date) >= NEXT(start_date)  
);

Which, for the sample data:

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

Outputs:

ID | START_DATE          | END_DATE           
-: | :------------------ | :------------------
 1 | 2019-10-11 00:00:00 | 2019-10-11 00:00:00
 1 | 2019-11-04 00:00:00 | 2019-12-20 00:00:00
 1 | 2020-01-01 00:00:00 | 2020-03-25 00:00:00

db<>fiddle here