I am trying to create start and end date range based on Active flag status for the following dataset. The TYPE status is active when Active Flag = Y and it will be effective until Active status change to N. The TYPE status is InActive when Active Flag = N and it will be effective until Active status change to Y. Based on this logic, I want to derived End_Date for every rows. It maintains same End date if subsequent row has same active flag and change when Active flag switched. The end result should look like derived Expected Results table as below
drop table PolicyBenefit;
create table PolicyBenefit(PN int,
Type varchar(1),
EffectiveDate date,
Active varchar(1)
);
delete from PolicyBenefit
insert into PolicyBenefit values (1,'D', TO_DATE('2017-03-15', 'yyyy/mm/dd'), 'N');
insert into PolicyBenefit values (1,'D', TO_DATE('2017/03/22', 'yyyy/mm/dd'), 'Y');
insert into PolicyBenefit values (1,'D', TO_DATE('2017/07/02', 'yyyy/mm/dd'), 'Y');
insert into PolicyBenefit values (1,'D', TO_DATE('2017/12/15', 'yyyy/mm/dd'), 'N');
insert into PolicyBenefit values (1,'D', TO_DATE('2018/02/03', 'yyyy/mm/dd'), 'Y');
insert into PolicyBenefit values (1,'D', TO_DATE('2018/05/06', 'yyyy/mm/dd'), 'N');
insert into PolicyBenefit values (1,'D', TO_DATE('2018/05/12' , 'yyyy/mm/dd'), 'N');
insert into PolicyBenefit values (1,'D', TO_DATE('2018/06/15', 'yyyy/mm/dd'), 'Y');
insert into PolicyBenefit values (1,'I', TO_DATE('2017/03/15', 'yyyy/mm/dd'), "N');
insert into PolicyBenefit values (1,'I', TO_DATE('2017/03/22' , 'yyyy/mm/dd'), 'N');
insert into PolicyBenefit values (1,'I', TO_DATE('2017/07/02', 'yyyy/mm/dd'), 'N');
Expected Results
| Pn | Type | StartDate | EndDate | Active |
| 1 | D | 2017/03/15 | 2017/03/22 | N |
| 1 | D | 2017/03/22 | 2017/12/15 | Y |
| 1 | D | 2017/07/02 | 2017/12/15 | Y |
| 1 | D | 2017/12/15 | 2018/02/03 | N |
| 1 | D | 2018/02/03 | 2018/05/06 | Y |
| 1 | D | 2018/05/06 | 2018/06/15 | N |
| 1 | D | 2018/05/12 | 2018/06/15 | N |
| 1 | D | 2018/06/15 | 9999/12/31 | Y |
| 1 | I | 2017/03/15 | 9999/12/31 | N |
| 1 | I | 2017/03/22 | 9999/12/31 | N |
| 1 | I | 2017/07/02 | 9999/12/31 | N |
I tried find End Date for group using LAST_VALUE "BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW" partition by type, effective date on Active flag, but didn't result correct date.
,LAST_VALUE(EffectiveDate) over (PARTITION BY pn, Type order by Active
between unbounded preceding and unbounded following) AS EndDate
Unfortunately, I do not have Oracle 12.1 installed to use MATCH_RECOGNIZE