我有以下代码,这些代码为我提供了三十天的生产日期和生产量。
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
注意:缺少日期(20/04/2015,23/04/2015至03/05/2015)
从sysdate开始,范围始终为三十天。您如何填写缺失的日期?您需要某种日历表吗?
谢谢
您可以从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','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)
)
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
上方。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句