Group by week and show even week that doesn't have any data

Question

I have query that pulls data by week and groups it together. But it 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

    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);

*****************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. Also now when I update the query based on suggestions below i am not getting any data even though I am getting dates and week number

Data that should be retuned:

Did you search for `[tsql] missing week`? – HABO Apr 16 '20 at 16:28 — HABO, Apr 16 '20 at 16:28

Yogesh Sharma · Accepted Answer · 2020-04-20T17:43:30.623

1

You can use recursive cte to generate calendar dates:

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);

edited Apr 20 '20 at 17:43

answered Apr 16 '20 at 15:50

Yogesh Sharma

49,870
5
26
52

When run this query and increse the range of date to one year It gives following error Msg 530, Level 16, State 1, Line 1 The statement terminated. The maximum recursion 100 has been exhausted before statement completion. – Learner Apr 16 '20 at 17:41
@Learner. . . By default recursive cte has 100 recursion. If you have more then you can use query hint. See updated. – Yogesh Sharma Apr 16 '20 at 17:44
Hi @ yogesh-sharma, This works great but is not returning any data for weeks that have data. its returning everything as null – Learner Apr 20 '20 at 17:31
@Learner. . You need to assign range in `ON` clause. – Yogesh Sharma Apr 20 '20 at 17:43
@ yogesh-sharma i actaully updated my query based on your suggestions and I am not getting any data back for weeks even when there is data. – Learner Apr 20 '20 at 18:36

Group by week and show even week that doesn't have any data

1 Answers1