即使没有数据，也显示一年中的每个星期 [英] Show every week of the Year even if there is no data

问题描述

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

I have query that pulls data by week and groups it together. But i does not display weeks that doesn't have any data. I want show all weeks even if they don't have data as null maybe

这里是查询，如果有人可以帮助我，那就太棒了

Here is the query if someone can help me with this it will awesome

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.

***************Output*************** As you could see week 2 and 4 is missing out since there is no data being returned. I would still like to see week 2 and 4 in the output with maybe 0 as result.

WeekOf 到 WeekNumber2019-12-29 00:00:00.000 2020-01-04 00:00:00.000 12020-01-12 00:00:00.000 2020-01-18 00:00:00.000 32020-01-26 00:00:00.000 2020-02-01 00:00:00.000 5

WeekOf to 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 可能包含时间组件，因此需要合并日期.

You probably need a calendar table. Here is a quick way of generating one, with an untested implementation of your code. I am assuming that the StartDate may contain a time component thus the need to coalesce the dates.

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)

这篇关于即使没有数据，也显示一年中的每个星期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！