Get data in groups of "Week of..." when dates may be missing

Question

I have data in a table with dates, and want to count the rows by "Week of" (e.g., "Week of 2017-05-01"), where the result has the week's date (starting on Mondays) and the count of matching rows — even if there are no rows for that week. (This will all be in a date range.)

I can partition things into weeks readily enough by grouping on DATEPART(wk, D) (where D is the date column), but I'm struggling with:

1. How to get the "Week of" date and fill, and

2. How to have a row for a week where there are no matching rows in the data

Here's grouping by week:

SET DATEFORMAT ymd;
SET DATEFIRST 1; -- Monday is first day of week

DECLARE @startDate DATETIME = '2017-05-01';
DECLARE @endDate DATETIME = '2017-07-01';

SELECT      DATEPART(wk, D) AS [Week Number], COUNT(*) AS [Count]
FROM        #temp
GROUP BY    DATEPART(wk, D)
ORDER BY    DATEPART(wk, D);

Which gives me:

+−−−−−−−−−−−−−+−−−−−−−+
| Week Number | Count |
+−−−−−−−−−−−−−+−−−−−−−+
| 19          |     5 |
| 20          |    19 |
| 22          |     8 |
| 23          |    10 |
| 24          |     5 |
| 26          |     4 |
+−−−−−−−−−−−−−+−−−−−−−+

But ideally I want:

+−−−−−−−−−−−−+−−−−−−−+
| Week       | Count |
+−−−−−−−−−−−−+−−−−−−−+
| 2017-05-01 |     5 |
| 2017-05-08 |    19 |
| 2017-05-15 |     0 |
| 2017-05-22 |     8 |
| 2017-05-29 |    10 |
| 2017-06-05 |     5 |
| 2017-06-12 |     0 |
| 2017-06-19 |     4 |
| 2017-06-26 |     0 |
+−−−−−−−−−−−−+−−−−−−−+

How can I do that?

---

Set up information for testing:

SET DATEFIRST 1;
SET DATEFORMAT ymd;

CREATE TABLE #temp (
    D DATETIME
);
GO

INSERT INTO #temp (D)
VALUES      -- Week of 2017-05-01 (#19)
            ('2017-05-01'),('2017-05-01'),('2017-05-01'),
            ('2017-05-06'),('2017-05-06'),
            -- Week of 2017-05-08 (#20) - note no data actually on the 8th
            ('2017-05-10'),
            ('2017-05-11'),('2017-05-11'),('2017-05-11'),('2017-05-11'),('2017-05-11'),('2017-05-11'),
            ('2017-05-12'),('2017-05-12'),('2017-05-12'),('2017-05-12'),
            ('2017-05-13'),('2017-05-13'),('2017-05-13'),('2017-05-13'),('2017-05-13'),('2017-05-13'),('2017-05-13'),
            ('2017-05-14'),
            -- Week of 2017-05-15 (#21)
            -- (note we have no data for this week)
            -- Week of 2017-05-22 (#22)
            ('2017-05-22'),('2017-05-22'),('2017-05-22'),
            ('2017-05-23'),('2017-05-23'),('2017-05-23'),('2017-05-23'),('2017-05-23'),
            -- Week of 2017-05-29 (#23)
            ('2017-05-29'),('2017-05-29'),('2017-05-29'),
            ('2017-06-02'),('2017-06-02'),
            ('2017-06-03'),
            ('2017-06-04'),('2017-06-04'),('2017-06-04'),('2017-06-04'),
            -- Week of 2017-06-05 (#24) - note no data actually on the 5th
            ('2017-06-08'),('2017-06-08'),('2017-06-08'),
            ('2017-06-11'),('2017-06-11'),
            -- Week of 2017-06-12 (#25)
            -- (note we have no data for this week)
            -- Week of 2017-06-19 (#26)
            ('2017-06-19'),('2017-06-19'),('2017-06-19'),
            ('2017-06-20');
GO

Answer 1

To do this, you have to generate a table or CTE with the Monday dates and their week numbers (as shown in this answer, slightly modified for what we need to do below), then LEFT JOIN or OUTER APPLY that with your data grouped by week, using the week numbers:

SET DATEFORMAT ymd;
SET DATEFIRST 1;

DECLARE @startDate DATETIME = '2017-05-01';
DECLARE @endDate DATETIME = '2017-07-01';

;WITH Mondays AS (
    SELECT  @startDate AS D, DATEPART(WK, @startDate) AS W
    UNION ALL
    SELECT  DATEADD(DAY, 7, D), DATEPART(WK, DATEADD(DAY, 7, D))
    FROM    Mondays m
    WHERE   DATEADD(DAY, 7, D) < @endDate
)
SELECT      LEFT(CONVERT(NVARCHAR(MAX), Mondays.D, 120), 10) AS [Week Of], d.Count
FROM        Mondays
OUTER APPLY (
            SELECT  COUNT(*) AS [Count]
            FROM    #temp
            WHERE   DATEPART(WK, D) = W
            AND     D >= @startDate
            AND     D < @endDate
) d
ORDER BY    Mondays.D;

Two notes on that:

1. I'm assuming we can ensure that @startDate is a Monday, which is easily done outside the query or could be done with a simple loop in T-SQL if needed (backing up until WEEKPART(WEEKDAY, @startDate) is 1). (Or worst case we could generate all the dates and then filter them with WEEKPART(WEEKDAY, ...).)

2. I'm assuming the date range is always a year or less; otherwise, we'd have duplicated week numbers. If the date range could be longer than a year, combine the week number with the year everywhere we're just using a week number above (e.g., DATEPART(YEAR, D) * 100 + DATEPART(wk, D)).

Answer 2

You can use this.

SET DATEFORMAT ymd;
SET DATEFIRST 1; -- Monday is first day of week

DECLARE @startDate DATETIME = '2017-05-01';
DECLARE @endDate DATETIME = '2017-07-01';

;WITH OrgResult AS ( -- Grouping result with missing week. Answer of the first question
    SELECT 
        DATEADD(DAY, 1 - DATEPART (WEEKDAY, D), D) [Week] -- Fist Day Of the Week
        , COUNT(*) [Count]
    FROM #temp
        WHERE D BETWEEN @startDate AND @endDate
    GROUP BY 
        DATEADD(DAY, 1 - DATEPART (WEEKDAY, D), D)
)
, Result AS -- Adds only missing weeks. Answer of the second question
(
    SELECT * FROM OrgResult
    UNION ALL
    SELECT DATEADD( DAY, 7, R.[Week] ), 0 [Count] 
    FROM Result R 
    WHERE NOT EXISTS( SELECT * FROM OrgResult O WHERE [Week] = DATEADD( DAY, 7, R.[Week] ) )
            AND DATEADD( DAY, 7, R.[Week] ) <= @endDate
)
SELECT * FROM Result
ORDER BY [Week]

Result:

Week        Count
----------- -----------
2017-05-01  5
2017-05-08  19
2017-05-15  0
2017-05-22  8
2017-05-29  10
2017-06-05  5
2017-06-12  0
2017-06-19  4
2017-06-26  0

Answer 3

Here's another approach. I included this as it will generate less reads than the Recursive CTE Solution and will be a lot fast

WITH E(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(x)),
iTally(N) AS 
(
  SELECT TOP (((DATEDIFF(day,@startdate, @endDate))/7)+1)
    (ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1)
  FROM E a, E b, E c
)
SELECT WeekOf = DATEADD(WEEK,N,@startDate), [count] = COUNT(t.D)
FROM iTally i
LEFT JOIN #temp t ON t.D >= DATEADD(WEEK,N,@startDate) AND t.D < DATEADD(WEEK,N+1,@startDate)
GROUP BY DATEADD(WEEK,N,@startDate)
ORDER BY DATEADD(WEEK,N,@startDate); -- not required

Results:

WeekOf     count
---------- -----------
2017-05-01 5
2017-05-08 19
2017-05-15 0
2017-05-22 8
2017-05-29 10
2017-06-05 5
2017-06-12 0
2017-06-19 4
2017-06-26 0