Expand and Fill the missing dates with last not null value

 

 

 

 

create table tx_data(dt date, amnt number);

 

insert into tx_data values(to_date('20-JUL-2019','DD-MON-YYYY'),100);

insert into tx_data values(to_date('25-JUL-2019','DD-MON-YYYY'),200);

insert into tx_data values(to_date('30-JUL-2019','DD-MON-YYYY'),300);

 

commit;

select min(dt) mi,max(dt)mx,max(dt)-min(dt) num from tx_data;

with d as (select min(dt) mi,max(dt)mx,max(dt)-min(dt) num from tx_data)

select rownum from d connect by level<=num;

 

with d as (select min(dt) mi,max(dt)mx,max(dt)-min(dt) num from tx_data)

select level-1+mi dt from d connect by level<=num;

 

with d as (select min(dt) mi,max(dt)mx,max(dt)-min(dt) num from tx_data)

select level-1+mi dt from d connect by level<=num+1;

 

with d as (select min(dt) mi,max(dt)mx,max(dt)-min(dt) num from tx_data),

d1 as (select level-1+mi dt from d connect by level<=num+1)

select * from d1,tx_data

where d1.dt=tx_data.dt(+);

 

with d as (select min(dt) mi,max(dt)mx,max(dt)-min(dt) num from tx_data),

d1 as (select level-1+mi dt from d connect by level<=num+1)

select d1.dt,amnt from d1,tx_data

where d1.dt=tx_data.dt(+)

order by d1.dt;

 

with d as (select min(dt) mi,max(dt)mx,max(dt)-min(dt) num from tx_data),

d1 as (select level-1+mi dt from d connect by level<=num+1)

select d1.dt,last_value(amnt ignore nulls) over(order by d1.dt)  amnt from d1,tx_data

where d1.dt=tx_data.dt(+)

order by d1.dt;