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

Issue

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!

Solution

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

Answered By – MT0

Answer Checked By – Katrina (AngularFixing Volunteer)

Leave a Reply

Your email address will not be published.