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!

CodePudding user response：

with 
  -- 1. Mark the start of each new protection period
  period_starts as (
    select 
      p.*,
      case 
        when protection_start_date > lag(protection_expiry_date) over(partition by objectid order by protection_start_date) 
        then 1
        else 0 
      end new_period
    from protections p
  ),
  
  -- 2. From each period start, make up a grouping by running cumulative sum for objectids
  periods as (
    select 
      p.*,
      sum(new_period) over(partition by objectid order by protection_start_date) period_group
    from period_starts p
  )
  
  -- 3. For each period group, calculate min(start) and max(end) date
select 
  objectid,
  min(protection_start_date) protection_start_date,
  max(protection_expiry_date) protection_expiry_date
from periods
group by objectid, period_group

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

db<>fiddle here

CodePudding user response：

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