create table policy_periods ( policy_id number(10) , start_date date , end_date date , in_or_out varchar2(1) ) / insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('10-10-2010','DD-MM-YYYY'), to_date('15-05-2011','DD-MM-YYYY'), 'I'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('15-10-2010','DD-MM-YYYY'), to_date('19-10-2010','DD-MM-YYYY'), 'O'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('20-11-2011','DD-MM-YYYY'), to_date('04-06-2012','DD-MM-YYYY'), 'I'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('05-04-2012','DD-MM-YYYY'), to_date('12-08-2012','DD-MM-YYYY'), 'I'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('14-02-2011','DD-MM-YYYY'), to_date('10-09-2011','DD-MM-YYYY'), 'O'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('01-02-2012','DD-MM-YYYY'), to_date('15-04-2012','DD-MM-YYYY'), 'O'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('01-06-2012','DD-MM-YYYY'), to_date('21-07-2012','DD-MM-YYYY'), 'O'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('31-10-2012','DD-MM-YYYY'), to_date('01-03-2013','DD-MM-YYYY'), 'I'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (1, to_date('31-01-2013','DD-MM-YYYY'), to_date('01-06-2013','DD-MM-YYYY'), 'I'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (2, to_date('20-11-2010','DD-MM-YYYY'), to_date('24-06-2011','DD-MM-YYYY'), 'I'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (2, to_date('05-04-2011','DD-MM-YYYY'), to_date('12-08-2012','DD-MM-YYYY'), 'I'); insert into policy_periods ( policy_id, start_date, end_date, in_or_out) values (2, to_date('14-02-2010','DD-MM-YYYY'), to_date('10-09-2011','DD-MM-YYYY'), 'O'); IN: 05-04-2011------------------12-08-2012 20-11-2010----24-06-2011 OUT: 14-02-2010----------------------- 10-09-2011 resulting: 11-09-2011-------12-08-2012 IN: 10-10-2010------------15-05-2011 20-11-2011----04-06-2012 31-01-2013----01-06-2013 05-04-2012----12-08-2012 31-10-2012----01-03-2013 OUT: 15/10-19/10 14-02-2011----10-09-2011 01-02-2012----15-04-2012 01-06-2012----21-07-2012 resulting: 10/10-14/10 20/10---13-02-2011 20-11-2011----31-01-2012 16-4-12----31-5-12 22-07-2012----12-08-2012 31-10-2012----01-06-2013 POLICY_ID INCLUSION_BEGIN INCLUSION_END ----------- ------------------------- ------------------------- 1 10-OCT-10 13-FEB-11 1 20-NOV-11 31-JAN-12 1 16-APR-12 31-MAY-12 1 22-JUL-12 12-AUG-12 1 31-OCT-12 01-JUN-13 2 11-SEP-11 12-AUG-12 with fused_periods as ( select distinct policy_id , in_or_out, period+1 period , first_value(start_date) over ( partition by policy_id, in_or_out, period order by start_date ) period_start , last_value(end_date) over ( partition by policy_id, in_or_out , period order by start_date rows between unbounded preceding and unbounded following ) period_end from ( select pps.* , sum(start_new_period) over ( partition by policy_id, in_or_out order by start_date) period from ( select policy_id, start_date, end_date, in_or_out , lag(end_date) over ( partition by policy_id, in_or_out order by start_date) previous_period_end , case when start_date > lag(end_date) over ( partition by policy_id, in_or_out order by start_date ) then 1 else 0 end start_new_period from policy_periods ) pps ) ) , inclusion_segments as ( select fp.policy_id , fp.period , fp.period_start , fp.period_end , nfp.period_start exclusion_start , nfp.period_end exclusion_end from fused_periods fp join -- join all inclusions with each overlapping exclusion (one that ends after the inclusion start and starts before the inclusion end) fused_periods nfp on (fp.policy_id = nfp.policy_id and nfp.period_end >= fp.period_start and nfp.period_start <= fp.period_end and nfp.in_or_out ='O' ) where fp.in_or_out ='I' union all -- add one extra row for each inclusion = select fp.policy_id , -1*fp.period , fp.period_start , fp.period_end , null , null from fused_periods fp where fp.in_or_out ='I' ) , enriched_inclusion_segments as ( select i.* , lag(exclusion_end) over (partition by policy_id, abs(period) order by period desc, exclusion_start) prev_ex_end from inclusion_segments i ) select policy_id , case when period < 0 -- the added row for the case without exclusion -- or for the stretch of segment after last exclusion then nvl(prev_ex_end + 1, period_start) -- when prev_ex_end is null, then no exclusion overlap at all else nvl(prev_ex_end+1, period_start) -- either start just after the previous exclusion end --or at the start of the period for the first row in the period end inclusion_start , case when period < 0 -- the added row then period_end else exclusion_start-1 -- until the start of the joined exclusion end inclusion_end from enriched_inclusion_segments es where (period < 0 and nvl(prev_ex_end, period_end-1) < period_end ) -- the added row: either no exclusion or an exclusion ending before the period end or (period > 0 and nvl(exclusion_start , period_start+1) > period_start) -- the joined exclusion should start after the start of the period order by policy_id