我有以下代码,它给出了30天的生产日期和生产量.
select (case when trunc(so.revised_due_date) <= trunc(sysdate) then trunc(sysdate) else trunc(so.revised_due_date) end) due_date,(case when (case when sp.pr_typ in ('VV','VD') then 'DVD' when sp.pr_typ in ('RD','CD') then 'CD' end) = 'CD' and (case when so.tec_criteria in ('PI','MC') then 'XX' else so.tec_criteria end) = 'OF' then sum(so.revised_qty_due) end) CD_OF_VOLUME from shop_order so left join scm_prodtyp sp on so.prodtyp = sp.prodtyp where so.order_type = 'MD' and so.plant = 'W' and so.status_code between '4' and '8' and trunc(so.revised_due_date) <= trunc(sysdate)+30 group by trunc(so.revised_due_date),so.tec_criteria,sp.pr_typ order by trunc(so.revised_due_date)
我遇到的问题是没有计划生产的日期,日期不会出现在报告上.有没有办法填写缺少的日期.
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
注意:缺少日期(2015年4月20日,2015年4月23日至2015年5月3日)
范围始终是sysdate的30天.
你如何填写缺少的日期?
你需要某种日历表吗?
谢谢
您可以从SYSDATE获取30天的时间段(我假设您要包含SYSDATE?):
原文链接:https://www.f2er.com/oracle/205301.htmlWITH mydates AS ( SELECT TRUNC(SYSDATE) - 1 + LEVEL AS due_date FROM dual CONNECT BY LEVEL <= 31 )
然后使用上面的代码对您的查询进行LEFT JOIN(将查询放入CTE也许不是一个坏主意):
WITH mydates AS ( SELECT TRUNC(SYSDATE) - 1 + LEVEL AS due_date FROM dual CONNECT BY LEVEL <= 31 ),myorders AS ( select (case when trunc(so.revised_due_date) <= trunc(sysdate) then trunc(sysdate) else trunc(so.revised_due_date) end) due_date,(case when (case when sp.pr_typ in ('VV','CD') then 'CD' end) = 'CD' and (case when so.tec_criteria in ('PI','MC') then 'XX' else so.tec_criteria end) = 'OF' then sum(so.revised_qty_due) end) CD_OF_VOLUME from shop_order so left join scm_prodtyp sp on so.prodtyp = sp.prodtyp where so.order_type = 'MD' and so.plant = 'W' and so.status_code between '4' and '8' and trunc(so.revised_due_date) <= trunc(sysdate)+30 group by trunc(so.revised_due_date),sp.pr_typ order by trunc(so.revised_due_date) ) SELECT mydates.due_date,myorders.cd_of_volume FROM mydates LEFT JOIN myorders ON mydates.due_date = myorders.due_date;
如果要在“缺失”日期而不是NULL上显示零,请使用上面的COALESCE(myorders.cd_of_volume,0)AS cd_of_volume.