I have a data set that is updated daily. Sometimes, the data is not completely updated with new rows. On these days, I would like the data to fill in:
- daily with the most recently available value ("copy down")
- but only fill down the rows when they exist for the max date for a given value of dim1 (i.e. do not fill in rows for when the dim1/dim2 combination does not exist for the date equal to the max date for that dim1 value)
- fill up to the maximum date that is available anywhere in the data set (don't fill in beyond the maximum date).
I've tried using lead and connect by but functions but am going in circles. Any help is appreciated.
Thanks
Jason
Oracle 11gR2
create table delete_me (dim1 char(3), dim2 char(1), date1 date, value1 int);
-- MODIFIED dates (no longer using sysdate)
insert into delete_me values ('FMC','A',to_date('01-Jun-2019','DD-mon-yyyy'), 2);
insert into delete_me values ('FMC','A',to_date('02-Jun-2019','DD-mon-yyyy'), 7);
insert into delete_me values ('FMC','A',to_date('03-Jun-2019','DD-mon-yyyy'), 7);
insert into delete_me values ('FMC','A',to_date('04-Jun-2019','DD-mon-yyyy'), 7);
insert into delete_me values ('FMC','A',to_date('05-Jun-2019','DD-mon-yyyy'), 2);
insert into delete_me values ('FMC','B',to_date('01-Jun-2019','DD-mon-yyyy'), 1);
insert into delete_me values ('FMC','B',to_date('02-Jun-2019','DD-mon-yyyy'), 1);
insert into delete_me values ('FMC','B',to_date('03-Jun-2019','DD-mon-yyyy'), 1);
insert into delete_me values ('FMC','B',to_date('04-Jun-2019','DD-mon-yyyy'), 1);
insert into delete_me values ('SCH','C',to_date('01-Jun-2019','DD-mon-yyyy'), 8);
insert into delete_me values ('SCH','C',to_date('02-Jun-2019','DD-mon-yyyy'), 8);
insert into delete_me values ('SCH','C',to_date('03-Jun-2019','DD-mon-yyyy'), 8);
insert into delete_me values ('SCH','C',to_date('04-Jun-2019','DD-mon-yyyy'), 8);
insert into delete_me values ('SCH','Z',to_date('01-Jun-2019','DD-mon-yyyy'), 8);
insert into delete_me values ('SCH','Z',to_date('02-Jun-2019','DD-mon-yyyy'), 8);
insert into delete_me values ('CRH','A',to_date('01-Jun-2019','DD-mon-yyyy'), 0);
insert into delete_me values ('CRH','A',to_date('02-Jun-2019','DD-mon-yyyy'), 3);
insert into delete_me values ('CRH','D',to_date('01-Jun-2019','DD-mon-yyyy'), 4);
commit;
select * from delete_me;
Wanted output:
dim1 dim2 date1 value1
FMC A 01-Jun-2019 2
FMC A 02-Jun-2019 7
FMC A 03-Jun-2019 7
FMC A 04-Jun-2019 7
FMC A 05-Jun-2019 2 << use this as the max date to fill up to (even if sysdate is far beyond this)
FMC B 01-Jun-2019 1
FMC B 02-Jun-2019 1
FMC B 03-Jun-2019 1
FMC B 04-Jun-2019 3
FMC B 05-Jun-2019 1 * added row
SCH C 01-Jun-2019 8
SCH C 02-Jun-2019 7
SCH C 03-Jun-2019 6
SCH C 04-Jun-2019 8
SCH C 05-Jun-2019 8 * added row
SCH Z 01-Jun-2019 5
SCH Z 02-Jun-2019 5 ** do not fill forward beyond this date because the most recent date for dim1 is only Jun 04
CRH A 01-Jun-2019 0
CRH A 02-Jun-2019 3
CRH A 03-Jun-2019 3 * added row
CRH A 04-Jun-2019 3 * added row
CRH A 05-Jun-2019 3 * added row
CRH D 01-Jun-2019 4
CRH D 02-Jun-2019 4 * added row
CRH D 03-Jun-2019 4 * added row
CRH D 04-Jun-2019 4 * added row
CRH D 05-Jun-2019 4 * added row
Message was edited by: Jason_S (hard coded date inserts)