0

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

I/p enter image description here

enter image description here

Yatindra Kumar Janghel
  • 95
  • 1
  • 6
  • 1
    Sample data *as text tables* would help. Personally, I would also find a standard date format (YYYY-MM-DD) easier to follow. – Gordon Linoff Oct 30 '20 at 12:39

1 Answers1

0

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

MT0
  • 143,790
  • 11
  • 59
  • 117
  • The `DEFINE` condition is the correct one when intervals are closed to the left and open to the right (as is generally considered "standard"). Looking at the OP's data, it is almost certain that his intervals are closed at both ends (otherwise his first row is an empty date interval). To fix that, you need to subtract 1 from the RHS - so that if two intervals end and then start on consecutive dates, they are fused together. Also, the OP wanted to give a unique ID to each row in the output. You can do that easily by adding `MATCH_NUMBER()` to the `MEASURES` clause. –  Oct 30 '20 at 14:26
  • @MT0 , thanks for the response , but match_recognize function is in oracle 12c , Can we apply some windowing clause to achieve this 11g – Yatindra Kumar Janghel Nov 01 '20 at 12:29