按周分组并显示没有任何数据的偶数周

学习者

我有按周提取数据并将其分组的查询。但它不会显示没有任何数据的周。即使他们没有数据为空，我也想显示所有星期

这是查询是否有人可以帮助我解决这个问题会很棒

    with calender_cte as (
     select convert(date, '2020-01-01') as startdate, convert(date, '2020-02-01') as enddate
     union all
     select dateadd(day, 1, startdate), enddate
     from calender_cte cc
     where startdate < enddate     
)
SELECT 
       min(Type) as Type,
       DATEADD (week, datediff(week, 0, cc.StartDate), -1) as 'WeekOf',
       DATEADD (week, datediff(week, 0, cc.StartDate), +5) as 'to',
       ISNULL(DATEPART(wk, inst.StartDate), 0) as 'WeekNumber',
       Sum(CASE When Type='Outage' Then (DATEDIFF(MINUTE, inst.StartDate,inst.EndDate)) Else 0 End) as 'OutageInMinutes',
       Sum(CASE When Type='Degradation' Then (DATEDIFF(MINUTE, inst.StartDate,inst.EndDate)) Else 0 End) as 'DegradationInMinutes'
FROM calender_cte cc LEFT JOIN
     [DESOutage].[dbo].[OPSInterruption] inst
     ON CC.startdate >=  inst.startdate and CC.startdate <= inst.enddate
Group by DATEADD (week, datediff(week, 0, cc.StartDate), -1),
         ISNULL(DATEPART(wk, cc.StartDate), 0),
         DATEADD (week, datediff(week, 0, cc.StartDate), +5)
option (maxrecursion 0);

*****************输出*****************

正如您所看到的，第 2 周和第 4 周错过了，因为没有返回数据。我仍然希望在输出中看到第 2 周和第 4 周，结果可能为 0。现在，当我根据下面的建议更新查询时，即使我得到了日期和周数，也没有得到任何数据

应重新调整的数据：

约格什·夏尔马

您可以使用递归 cte 生成日历日期：

with calender_cte as (
     select convert(date, '2020-01-01') as startdate, convert(date, '2020-02-01') as enddate
     union all
     select dateadd(day, 1, startdate), enddate
     from calender_cte cc
     where startdate < enddate     
)
SELECT DATEADD (week, datediff(week, 0, cc.StartDate), -1) as 'WeekOf',
       DATEADD (week, datediff(week, 0, cc.StartDate), +5) as 'to',
       ISNULL(DATEPART(wk, inst.StartDate), 0) as 'WeekNumber'
FROM calender_cte cc LEFT JOIN
     [DESOutage].[dbo].[OPSInterruption] inst
     ON CC.startdate >=  inst.startdate and CC.startdate <= inst.enddate
Group by DATEADD (week, datediff(week, 0, cc.StartDate), -1),
         DATEPART(wk, inst.StartDate),
         DATEADD (week, datediff(week, 0, cc.StartDate), +5)
option (maxrecursion 0);

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。