0

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

MT0
  • 143,790
  • 11
  • 59
  • 117
Roberto Amaro
  • 1
  • Assuming that your date format is `MM/DD/YY` then type `B` has an invalid `DATE_FROM`. – MT0 Aug 07 '23 at 20:21
  • Oracle SQL Developer is a client application used to talk to a database; it is **NOT** a database and the version of the client application is irrelevant to the problem (and generally, which client application you use is also irrelevant to the problem). What you need to tell us is what the version is of the Oracle database that you are connecting to. – MT0 Aug 07 '23 at 20:32

1 Answers1

0

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

MT0
  • 143,790
  • 11
  • 59
  • 117