Evalaute Oracle Table to Calulate or Extend Multiple Date Ranges into One Period

I have a table that shows date ranges of an object. For reference

• The object needs some sort of event in order to prevent it from expiring.
• We have a rule where we can apply a protection against the object from expiring, so that there is no need to action the object.
• The date ranges are a period of time that doesn’t allow the object to expire (the protection period)
• A protection can be applied and removed many times over the life of the object.

An example is the following:

OBJECTID	PROTECTION_START_DATE	PROTECTION_EXPIRY_DATE
843856	04-DEC-17	31-AUG-18
843856	11-OCT-18	31-OCT-18
843856	16-OCT-18	30-JUN-19
843856	28-MAY-19	31-OCT-19
843856	09-SEP-19	09-SEP-20
843856	09-SEP-19	31-DEC-21
843856	18-OCT-21	30-SEP-22

Sometimes a protection will expire on a date, but we will put an additional protection on the object on the same date the previous protection expired or even before the expiry date of the protection, in order to extend that protection further. What I would like to do is evaluate the table, determine where these situations arise and calculate the complete range of dates where these extended protection occur.

The end result would look something like the table below, where the first row is a unique protection period. When it expired, we didn’t add an additional protection on or before the expiry date. The next 6 rows kept extending the next protection, where it was initially applied on 11-OCT-18 (dd-mmm-yy date format), but on or before the expiry date, we kept extending the protection until the expiry date on the last row (30-SEP-22).

OBJECTID	PROTECTION_START_DATE	PROTECTION_EXPIRY_DATE
843856	04-DEC-17	31-AUG-18
843856	11-OCT-18	30-SEP-22

I have been playing with some cross joins to create some Cartesian tables, but I’m pretty new to that function and I am not sure how to handle this situation. Any suggestions on how to do this is very appreciated!

Answer

From Oracle 12, you can use MATCH_RECOGNIZE for row-by-row processing:

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
)

Which, for the sample data:

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;

Outputs:

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

db<>fiddle here