sql - 使用周数和周天格式的日期范围的总值?
问题描述
[每周结果汇总表
我想获得两个日期之间的汇总值....这是我的代码
SELECT CASE
WHEN DATEPART(dw,'2019-02-02') = 1THEN WD01
WHEN DATEPART(dw,'2019-02-02') = 2 THEN WD02
WHEN DATEPART(dw,'2019-02-02') = 3 THEN WD03
WHEN DATEPART(dw,'2019-02-02') = 4 THEN WD04
WHEN DATEPART(dw,'2019-02-02') = 5 THEN WD05
WHEN DATEPART(dw,'2019-02-02') = 6 THEN WD06
WHEN DATEPART(dw,'2019-02-02') = 7 THEN WD07
END AS VALUE,
CASE
WHEN DATEPART(dw,'2019-02-28') = 1THEN WD01
WHEN DATEPART(dw,'2019-02-28') = 2 THEN WD02
WHEN DATEPART(dw,'2019-02-28') = 3 THEN WD03
WHEN DATEPART(dw,'2019-02-28') = 4 THEN WD04
WHEN DATEPART(dw,'2019-02-28') = 5 THEN WD05
WHEN DATEPART(dw,'2019-02-28') = 6 THEN WD06
WHEN DATEPART(dw,'2019-02-28') = 7 THEN WD07
END AS VALUE2
FROM SummaryWeeklyTest
WHERE cWeekNum BETWEEN DATEPART(wk,'2019-02-03') AND DATEPART(wk,'2019-02-28')
输出应该是突出显示值的总和
解决方案
如果您将数据发布为 txt 而不是图像,我会给出一个完整的答案。然而,作为它的图像,这是未经测试的。应该给你的想法。
我要做的是先扩展数据,然后就可以轻松地做任何你想做的事情:
;with days AS (
select * from (values (1),(2),(3),(4),(5),(6),(7)) as t(d)
)
,expanded as (
select terminal, cyear, cWeekNum, d,
case d
when 1 then WD01
when 2 then WD02
when 3 then WD03
when 4 then WD04
when 5 then WD05
when 6 then WD06
when 7 then WD07
end as WD
from SummaryWeeklytest
cross join days
)
select sum(WD) from expanded
where cWeekNum BETWEEN DATEPART(wk,'2019-02-03') AND DATEPART(wk,'2019-02-28')