Split dates based on Start and End Dates

Question

I'm seeking help on date split, the dates can start between month also. below is the sample code and output. Some of splits can end middle due to person deceased.

Create Table #DATE_SPLIT
(
ID             INT,
StartDate      DATE,
EndDate        DATE,
Deceased_Date  DATE
)

INSERT INTO #DATE_SPLIT
SELECT 10,'01/10/2020','03/21/2020',NULL
UNION ALL
SELECT 10,'03/22/2020','12/31/9999',NULL
UNION ALL
SELECT 20,'01/01/2020','02/21/2020','03/20/2020'
UNION ALL
SELECT 20,'02/22/2020','12/31/9999','03/20/2020'



--OUTPUT
ID       StartDate    EndDate        Deceased_Date  
10     '01/10/2020'   '01/31/2020'    NULL
10     '02/01/2020'   '02/29/2020'    NULL
10     '03/01/2020'   '03/21/2020'    NULL
10     '03/22/2020'   '03/31/2020'    NULL
10     '04/01/2020'   '04/30/2020'    NULL
10     '05/01/2020'   '05/31/2020'    NULL
10     '06/01/2020'   '06/30/2020'    NULL
10     '07/01/2020'   '12/31/9999'    NULL


20     '01/10/2020'   '01/31/2020'    '03/20/2020'
20     '02/01/2020'   '02/21/2020'    '03/20/2020'
20     '02/22/2020'   '02/29/2020'    '03/20/2020'
20     '03/01/2020'   '03/20/2020'    '03/20/2020'     ---LOOP Has to end here, since member is decease. thanks!

Answer 1

Assuming you are actually using Oracle, and it's a fairly recent version - i.e. 11gR2 or higher, and not 10g as you tagged the question - then you can use recursive subquery factoring to perform the split. Further assuming that you don't want to split beyond the current month, you can use a case expression to decide whether to split or to keep the original (9999-12-31) end date or deceased date. Something like:

with rcte (id, startdate, enddate, deceased_date, full_enddate) as (
  select id,
    startdate,
    case
      when trunc(startdate, 'MM') = trunc(sysdate, 'MM')
        and enddate > sysdate then enddate
      when deceased_date is null then least(last_day(startdate), enddate)
      else least(last_day(startdate), enddate, deceased_date)
    end,
    deceased_date,
    enddate
  from date_split
  union all
  select id,
    enddate + 1,
    case
      when trunc(enddate + 1, 'MM') = trunc(sysdate, 'MM')
        and full_enddate > sysdate then full_enddate
      when deceased_date is null then least(last_day(enddate + 1), full_enddate)
      else least(last_day(enddate + 1), full_enddate, deceased_date)
    end,
    deceased_date,
    full_enddate
    from rcte
    where enddate < least(full_enddate, nvl(deceased_date, full_enddate))
)
select id, startdate, enddate, deceased_date
from rcte
order by id, startdate;

which with your sample data gets:

ID | STARTDATE  | ENDDATE    | DECEASED_DATE
-: | :--------- | :--------- | :------------
10 | 2020-01-10 | 2020-01-31 | null
10 | 2020-02-01 | 2020-02-29 | null
10 | 2020-03-01 | 2020-03-21 | null
10 | 2020-03-22 | 2020-03-31 | null
10 | 2020-04-01 | 2020-04-30 | null
10 | 2020-05-01 | 2020-05-31 | null
10 | 2020-06-01 | 2020-06-30 | null
10 | 2020-07-01 | 9999-12-31 | null
20 | 2020-01-01 | 2020-01-31 | 2020-03-20   
20 | 2020-02-01 | 2020-02-21 | 2020-03-20   
20 | 2020-02-22 | 2020-02-29 | 2020-03-20   
20 | 2020-03-01 | 2020-03-20 | 2020-03-20   

db<>fiddle

The anchor member gets the data from your table, preserves the original full end date, and decides that row's 'split' end date base on a case expression to check the current month, and then either the end date or deceased date.

If the row was split then the recursive member starts from the first day of the following month, still preserves the original full end date, and repeats the logic to decide that split's end date. It does that recursively (hence the name) until either the deceased date for original full end date has been reached.