ACCNO DOD AMT
100 02-JAN-10 2000
200 10-JAN-10 1000
100 08-JAN-10 4000
If you observe for accno 100 there are no entries after 02-JAN-2010 till 08-JAN-2010 and amt in this account will be 2000 till 07-JAN-2010 but if we require to generate intermediate values then following query is written to generate values
with date_range as
(
select max(dod) maxdt,min(dod) mindt from bank
),
date_seq as
(
select mindt+level-1 final_date from date_range
connect by level <= (maxdt-mindt)+1
)
select to_number( substr(
max( case when accno is not null
then to_char(date_seq.final_date,'yyyymmdd')||accno
end ) over (order by date_seq.final_date)
, 9 ) ) accno,date_seq.final_date
,to_number( substr(
max( case when amt is not null
then to_char(date_seq.final_date,'yyyymmdd')||amt
end ) over (order by date_seq.final_date)
, 9 ) ) amt
from bank,date_seq where date_seq.final_date=bank.dod(+) order by final_date
(
select max(dod) maxdt,min(dod) mindt from bank
),
date_seq as
(
select mindt+level-1 final_date from date_range
connect by level <= (maxdt-mindt)+1
)
select to_number( substr(
max( case when accno is not null
then to_char(date_seq.final_date,'yyyymmdd')||accno
end ) over (order by date_seq.final_date)
, 9 ) ) accno,date_seq.final_date
,to_number( substr(
max( case when amt is not null
then to_char(date_seq.final_date,'yyyymmdd')||amt
end ) over (order by date_seq.final_date)
, 9 ) ) amt
from bank,date_seq where date_seq.final_date=bank.dod(+) order by final_date