0

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)

***************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 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

Learner
  • 55
  • 1
  • 4
  • You also probably want `>=` for the `StartDate` comparison. – Joel Coehoorn Apr 16 '20 at 13:49
  • Does this answer your question? [SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns](https://stackoverflow.com/questions/12890967/show-all-dates-data-between-two-dates-if-no-row-exists-for-particular-date-then) – Eric Brandt Apr 16 '20 at 14:17
  • 3
    This has been asked and answered over and over again here (and elsewhere). The suggested link shows a few ways, but the short answer is that you'll need to generate a list of dates to join from, either from a calendar table, a numbers table, or a CTE or subquery that generates one of the above on an ad hoc basis. – Eric Brandt Apr 16 '20 at 14:19

1 Answers1

0

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)
Steve
  • 710
  • 1
  • 6
  • 12