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:
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).
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!
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;
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
Answered By – MT0
Answer Checked By – Katrina (AngularFixing Volunteer)