how to proof for overlapping Dates within groups in oracle sql

Question

I'm using oracle SQL Developer 21.4.3 to try to select records within groups without overlapping dates, however I haven't found a way to do this. This is an example of the table, we have to sets of dates, the technical date when the Record was created in the Table(DATE_FROM and DATE_TO) and the Expiration Date for the Product (VALID_FROM, VALID_TO)

Example (have)

KEY	TYPE	DATE_FROM	DATE_TO	VALID_FROM	VALID_TO
123456	A	10/31/22	12/31/99	12/31/11	02/28/25
123456	B	30/09/22	12/31/99	10/31/22	02/28/25
123456	C	10/31/22	12/31/99	02/28/25	08/31/35

Here the expiration Date for TYPE A and B are overlapping, at the end I only want to keep the Records with TYPE A and C, since these build a continous range using VALID_FROM and VALID_TO.

Example (want)

KEY	TYPE	DATE_FROM	DATE_TO	VALID_FROM	VALID_TO
123456	A	10/31/22	12/31/99	12/31/11	02/28/25
123456	C	10/31/22	12/31/99	02/28/25	08/31/35

I have tried using the function ROW_NUMBER by ( PARTITION and ORDER BY) to identify the correct records but so far has not worked

Thanks for your help

Answer 1

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row pattern matching and find the first row of each match (using {- -} to not output successive rows following an overlap):

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY valid_from, valid_to DESC
  ALL ROWS PER MATCH
  PATTERN ( overlapping {- overlapping* not_overlapping -} | not_overlapping )
  DEFINE
    overlapping AS MAX(valid_to) > NEXT(valid_from)
)

Which, for the sample data (fixing the type B DATE_FROM value):

CREATE TABLE table_name (KEY, TYPE, DATE_FROM, DATE_TO, VALID_FROM, VALID_TO) AS
SELECT 123456, 'A', DATE '2022-10-31', DATE '2099-12-31', DATE '2011-12-31', DATE '2025-02-28' FROM DUAL UNION ALL
SELECT 123456, 'B', DATE '2022-03-09', DATE '2099-12-31', DATE '2022-10-31', DATE '2025-02-28' FROM DUAL UNION ALL
SELECT 123456, 'C', DATE '2022-10-31', DATE '2099-12-31', DATE '2025-02-28', DATE '2035-08-31' FROM DUAL;

Outputs:

VALID_FROM	VALID_TO	KEY	TYPE	DATE_FROM	DATE_TO
2011-12-31 00:00:00	2025-02-28 00:00:00	123456	A	2022-10-31 00:00:00	2099-12-31 00:00:00
2025-02-28 00:00:00	2035-08-31 00:00:00	123456	C	2022-10-31 00:00:00	2099-12-31 00:00:00

fiddle