Restarting sum(over) function based on parameter -Oracle

Question

What I am attempting to do is use the sum over function in such a way that it resets to 0 and starts over when it encounters a parameter value. What I have enclosed is some basic membership data with begin and end dates. Now if there is a break between membership periods of more than 5 days, then it restarts the counter again. I attempted to use a lag function, but oracle is not allowing the windowing function.

Data:

ID  BEG_DATE    END_DATE         MONTHS DAYS_IN_COVERAGE_BREAK  tot_membership_months   
123 01-JAN-15   31-DEC-15        30.3                          30.3 first term
123 01-JAN-16   28-FEB-17        35.3   1                      65.6 --Adds first & 2nd term
123 01-JUN-17   31-DEC-17        17.8   93                     17.8 **--restarts- lapse in coverage**
123 01-JAN-18   30-MAR-19        37.8   1                      55.6 --Adds from restart time

Query:

With TL as (select '123' as id, to_Date('01JAN2015') as Beg_date,to_date('31DEC2015') as end_Date from dual union
            select '123' as id, to_Date('01JAN2016') as Beg_date,to_date('28FEB2017') as end_Date from dual union
            select '123' as id, to_Date('01JUN2017') as Beg_date,to_date('31DEC2017') as end_Date from dual union
            select '123' as id, to_Date('01JAN2018') as Beg_date,to_date('30MAR2019') as end_Date from dual)
            
select TL.*, round((END_DATE-BEG_DATE) /12,1) as MONTHS,
            BEG_DATE-LAG(END_DATE) OVER (PARTITION BY ID ORDER BY BEG_DATE) as DAYS_IN_COVERAGE_BREAK
            --case when BEG_DATE-LAG(END_DATE) OVER (PARTITION BY ID ORDER BY BEG_DATE)<5
           -- then sum(round((END_DATE-BEG_DATE) /12,1) over (partition by mrn order by beg_date)) else round((END_DATE-BEG_DATE) /12,1) end as member_cum_months
from TL;

Any insights appreciated. Thanks

Answer 1

This is a gaps-and-islands problem, where a new island starts for every row with a gap of 5 days of more with previous row.

Here is one option that uses window functions: lag() gives us the previous end_date, then we use a window sum that increments for every gap that is more than 5 days to define the group.

select id, beg_date, end_date, 
    end_date - min(beg_date) over(partition by id, grp order by beg_date) total_membership_days
from (
    select t.*,
        sum(case when beg_date > lag_end_date + interval '5' day then 1 else 0 end) over(partition by id order by beg_date) grp
    from (
        select t.*, 
            lag(end_date) over(partition by id order by beg_date) lag_end_date
        from tl t
    ) t
) t

If you want the result as months rather than days, then:

select id, beg_date, end_date, 
    months_between(end_date, min(beg_date) over(partition by id, grp order by beg_date)) total_membership_months
from (
    select t.*,
        sum(case when beg_date > lag_end_date + interval '5' day then 1 else 0 end) over(partition by id order by beg_date) grp
    from (
        select t.*, 
            lag(end_date) over(partition by id order by beg_date) lag_end_date
        from tl t
    ) t
) t