ORACLE SQL: Fill in missing dates -


i have following code gives me production dates , production volumes thirty day period.

select  (case when trunc(so.revised_due_date) <= trunc(sysdate)      trunc(sysdate) else trunc(so.revised_due_date) end) due_date,  (case      when (case when sp.pr_typ in ('vv','vd') 'dvd' when sp.pr_typ in ('rd','cd')      'cd' end) = 'cd'      ,  (case when so.tec_criteria in ('pi','mc')      'xx' else so.tec_criteria end) = 'of'     sum(so.revised_qty_due) end) cd_of_volume shop_order left join scm_prodtyp sp on so.prodtyp = sp.prodtyp so.order_type = 'md'  , so.plant = 'w'  , so.status_code between '4' , '8'  , trunc(so.revised_due_date) <= trunc(sysdate)+30 group trunc(so.revised_due_date), so.tec_criteria, sp.pr_typ order trunc(so.revised_due_date) 

the problem have there date no production planned, date wont appear on report. there way of filling in missing dates.

i.e. current report shows following ...

due_date    cd_of_volume  14/04/2015     35,267.00  15/04/2015     71,744.00  16/04/2015     20,268.00  17/04/2015     35,156.00  18/04/2015     74,395.00  19/04/2015      3,636.00  21/04/2015      5,522.00 22/04/2015     15,502.00 04/05/2015     10,082.00 

note: missing dates (20/04/2015, 23/04/2015 03/05/2015)

range thirty day period sysdate. how fill in missing dates? need kind of calendar table?

thanks

you can 30-day period sysdate follows (i assume want include sysdate?):

with mydates (     select trunc(sysdate) - 1 + level due_date dual    connect level <= 31 ) 

then use above left join query (perhaps not bad idea put query in cte well):

with mydates (     select trunc(sysdate) - 1 + level due_date dual    connect level <= 31 ), myorders (     select      (case when trunc(so.revised_due_date) <= trunc(sysdate)          trunc(sysdate) else trunc(so.revised_due_date) end) due_date,      (case          when (case when sp.pr_typ in ('vv','vd') 'dvd' when sp.pr_typ in ('rd','cd')          'cd' end) = 'cd'          ,  (case when so.tec_criteria in ('pi','mc')          'xx' else so.tec_criteria end) = 'of'         sum(so.revised_qty_due)     end) cd_of_volume     shop_order     left join scm_prodtyp sp     on so.prodtyp = sp.prodtyp     so.order_type = 'md'      , so.plant = 'w'      , so.status_code between '4' , '8'      , trunc(so.revised_due_date) <= trunc(sysdate)+30     group trunc(so.revised_due_date), so.tec_criteria, sp.pr_typ     order trunc(so.revised_due_date) ) select mydates.due_date, myorders.cd_of_volume   mydates left join myorders     on mydates.due_date = myorders.due_date; 

if want show 0 on "missing" dates instead of null, use coalesce(myorders.cd_of_volume, 0) cd_of_volume above.


Comments

Popular posts from this blog

css - SVG using textPath a symbol not rendering in Firefox -

Java 8 + Maven Javadoc plugin: Error fetching URL -

datatable - Matlab struct computations -