create table TEST_DTE_MERGE
(sak_recip number(8) not null,
sak_prov_loc number(8) not null,
sak_pub_hlth number(8) not null,
dte_eff number(8) not null,
dte_end number(8) not null);
insert into TEST_DTE_MERGE values (949242, 507, 1019, 20050603, 20070430);
insert into TEST_DTE_MERGE values (949242, 507, 1019, 20070501, 20141031);
insert into TEST_DTE_MERGE values (949242, 507, 1019, 20141101, 20180525);
insert into TEST_DTE_MERGE values (949242, 507, 1019, 20180605, 22991231);
insert into TEST_DTE_MERGE values (949242, 7398, 1019, 19990401, 20011231);
insert into TEST_DTE_MERGE values (949242, 7398, 1019, 20020101, 20040916);
insert into TEST_DTE_MERGE values (949242, 7398, 1019, 20050601, 20050603);
commit;
select count(*) from TEST_DTE_MERGE;
select * from TEST_DTE_MERGE order by
sak_recip,sak_prov_loc,sak_pub_hlth,dte_eff,dte_end;
SAK_RECIP SAK_PROV_LOC SAK_PUB_HLTH DTE_EFF DTE_END
---------- ------------ ------------ ---------- ------------------------------------------------------
949242 507 1019 20050603 20070430
949242 507 1019 20070501 20141031
949242 507 1019 20141101 20180525
949242 507 1019 20180605 22991231
949242 7398 1019 19990401 20011231
949242 7398 1019 20020101 20040916
949242 7398 1019 20050601 20050603
7 rows selected
If a given row for a given SAK_RECIP,SAK_PROV_LOC,SAK_PUB_HLTH,
- if DTE_EFF has the value 1 more than DTE_END from previous row,
then we need to merge the rows and update DTE_END as a DTE_END from latest row.
Expected results:
SAK_RECIP SAK_PROV_LOC SAK_PUB_HLTH DTE_EFF DTE_END
---------- ------------ ------------ ---------- -----------
949242 507 1019 20050603 20180525
949242 507 1019 20180605 22991231
949242 7398 1019 19990401 20040916
949242 7398 1019 20050601 20050603