我有以下代码,它给出了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？)：
WITH 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.
