datetime - Populate the monthly date from quarterly date in Oracle -
i have values in fact table below:
as of date f_type value 31-mar-17 abc corp 1.0 30-jun-17 abc corp 1.1
as of dates quarter end dates, need write query result set below:(month end dates between dates after value changed)
as of date f_type value 31-mar-17 abc corp 1.0 30-apr-17 abc corp 1.0 31-may-17 abc corp 1.0 30-jun-17 abc corp 1.1
how can populate 30-apr-17 , 31-may-17 rows??
code tried far:
select as_of_date, f_type, value,x.* fact f inner join (select m_date,trunc(m_date+1,'q')-1 qtr_date (select to_date('31-jan-2017','dd-mon-yyyy') m_date dual union select to_date('28-feb-2017','dd-mon-yyyy') dual union select to_date('31-mar-2017','dd-mon-yyyy') dual union select to_date('30-apr-2017','dd-mon-yyyy') dual union select to_date('31-may-2017','dd-mon-yyyy') dual union select to_date('30-jun-2017','dd-mon-yyyy') dual union select to_date('31-jul-2017','dd-mon-yyyy') dual union select to_date('31-aug-2017','dd-mon-yyyy') dual union select to_date('30-sep-2017','dd-mon-yyyy') dual union select to_date('31-oct-2017','dd-mon-yyyy') dual union select to_date('30-nov-2017','dd-mon-yyyy') dual union select to_date('31-dec-2017','dd-mon-yyyy') dual ) )x on f.as_of_date=x.qtr_date
this gives me below:
as of date f_type value m_date qtr_date 3/31/2017 abc corp 1.0 3/31/2017 3/31/2017 3/31/2017 abc corp 1.0 4/30/2017 3/31/2017 3/31/2017 abc corp 1.0 5/31/2017 3/31/2017 6/30/2017 abc corp 1.1 6/30/2017 6/30/2017 6/30/2017 abc corp 1.1 7/31/2017 6/30/2017 6/30/2017 abc corp 1.1 8/31/2017 6/30/2017
is there way tune query , output well.
i think below query may work out you, tried table , checked output.
select * fact union select add_months(as_of_date,lvl.lvl2) dt, fact.f_type, fact.value fact, (select level lvl2 dual connect level<=2) lvl ,(select max(as_of_date) dt2 fact) fc2 add_months(as_of_date,lvl.lvl2)<=fc2.dt2
i have taken below data set:
as of date f_type value 31-mar-17 abc corp 1.0 30-jun-17 abc corp 1.1 30-sep-17 abc corp 1.2
added sep row test authenticity of query.
and getting output as:
as of date f_type value 31-mar-17 abc corp 1.0 30-apr-17 abc corp 1.0 31-may-17 abc corp 1.0 30-jun-17 abc corp 1.1 31-jul-17 abc corp 1.1 30-aug-17 abc corp 1.1 30-sep-17 abc corp 1.2
i hope you're looking for.
Comments
Post a Comment