By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (OBJECTID, PROTECTION_START_DATE, PROTECTION_EXPIRY_DATE) AS
SELECT 843856, DATE '2017-12-04', DATE '2018-08-31' FROM DUAL UNION ALL
SELECT 843856, DATE '2018-10-11', DATE '2018-10-31' FROM DUAL UNION ALL
SELECT 843856, DATE '2018-10-16', DATE '2019-06-30' FROM DUAL UNION ALL
SELECT 843856, DATE '2019-05-28', DATE '2019-10-31' FROM DUAL UNION ALL
SELECT 843856, DATE '2019-09-09', DATE '2020-09-09' FROM DUAL UNION ALL
SELECT 843856, DATE '2019-09-09', DATE '2021-12-31' FROM DUAL UNION ALL
SELECT 843856, DATE '2021-10-18', DATE '2022-09-30' FROM DUAL;
7 rows affected
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT *
FROM table_name
MATCH_RECOGNIZE(
PARTITION BY objectid
ORDER BY protection_start_date
MEASURES
FIRST(protection_start_date) AS protection_start_date,
MAX(protection_expiry_date) AS protection_expiry_date
PATTERN (first_date overlapping*)
DEFINE
overlapping AS PREV(protection_expiry_date) >= protection_start_date
)
| OBJECTID | PROTECTION_START_DATE | PROTECTION_EXPIRY_DATE |
|---|---|---|
| 843856 | 2017-12-04 00:00:00 | 2018-08-31 00:00:00 |
| 843856 | 2018-10-11 00:00:00 | 2022-09-30 00:00:00 |