tsql - 即使没有数据也显示一年中的每个星期
问题描述
我有按周提取数据并将其组合在一起的查询。但我不显示没有任何数据的周。我想显示所有星期,即使他们没有数据为空
这是查询是否有人可以帮助我,这将是很棒的
SELECT
DATEADD (week, datediff(week, 0, StartDate), -1) as 'WeekOf'
,DATEADD (week, datediff(week, 0, StartDate), +5) as 'to'
,DATEPART(wk, StartDate) as 'WeekNumber'
FROM [DESOutage].[dbo].[OPSInterruption]
Where StartDate > '2020-01-01' and EndDate <'2020-02-01'
Group by DATEADD (week, datediff(week, 0, StartDate), -1),DATEPART(wk, StartDate),DATEADD (week, datediff(week, 0, StartDate), +5)
***************输出*************** 正如您所见,第 2 周和第 4 周丢失了,因为没有返回数据。我仍然希望在输出中看到第 2 周和第 4 周,结果可能为 0。
WeekOf 到 WeekNumber 2019-12-29 00:00:00.000 2020-01-04 00:00:00.000 1 2020-01-12 00:00:00.000 2020-01-18 00:00:00.000 3 2020-01-26 00:00:00.000 2020-02-01 00:00:00.000 5
解决方案
您可能需要一个日历表。这是一种生成方法的快速方法,其中包含未经测试的代码实现。我假设 StartDate 可能包含一个时间组件,因此需要合并日期。
DECLARE @StartYear DATETIME = '20200101'
DECLARE @days INT = 366
;WITH
E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), -- 1*10^1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), -- 1*10^2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), -- 1*10^4 or 10,000 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), -- 1*10^8 or 100,000,000 rows
Tally(N) AS (SELECT TOP (@Days) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E8),
Calendar AS (
SELECT StartOfDay = DATEADD(dd,N-1,@StartYear),
EndOfDay = DATEADD(second, -1, DATEADD(dd,N ,@StartYear))
FROM Tally)
SELECT DATEADD (week, datediff(week, 0, COALESCE(x.StartDate, c.StartOfDay) ), -1) as 'WeekOf'
, DATEADD (week, datediff(week, 0, COALESCE(x.StartDate, c.StartOfDay)), +5) as 'to'
, DATEPART(wk, COALESCE(x.StartDate, c.StartOfDay)) as 'WeekNumber'
FROM Calendar c
INNER JOIN [DESOutage].[dbo].[OPSInterruption] x
ON x.StartDate > c.StartOfDay AND x.StartDate <= c.EndOfDay
WHERE c.StartOfDay > '2020-01-01' AND c.StartOfDay <'2020-02-01'
GROUP BY DATEADD (week, datediff(week, 0, COALESCE(x.StartDate, c.StartOfDay)), -1),
DATEPART(wk, COALESCE(x.StartDate, c.StartOfDay)),
DATEADD (week, datediff(week, 0, COALESCE(x.StartDate, c.StartOfDay)), +5)
推荐阅读
- java - 我的服务在一个类中工作,但在另一个类中返回 null
- node.js - TS2339:“导航器”类型上不存在属性“联系人”
- python - 复制小型networkx图中未通过边连接的节点
- python - 如何计算数据框中每一行去年发生的案例数?
- python - Error when trying access parameters in AWS Python Lambda function using API Gateway
- postman - postman 在哪里输入非表单数据?
- c++ - 如何避免使用此向量进行隐式复制/破坏?
- python - 运行用户代理解析时的不同结果
- c# - 关于统一制作HUD雷达覆盖的建议
- html - 最小化屏幕时如何阻止盒子碰撞