title: Get data in groups of "Week of..." when dates may be missing
body: <p>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&nbsp;&mdash; even if there are no rows for that week. (This will all be in a date range.)</p>
  
  <p>I can partition things into weeks readily enough by grouping on <code>DATEPART(wk, D)</code> (where <code>D</code> is the date column), but I'm struggling with:</p>
  
  <ol>
  <li><p>How to get the "Week of" date and fill, and</p></li>
  <li><p>How to have a row for a week where there are no matching rows in the data</p></li>
  </ol>
  
  <p>Here's grouping by week:</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>Which gives me:</p>
  
  <pre>
  +−−−−−−−−−−−−−+−−−−−−−+
  | Week Number | Count |
  +−−−−−−−−−−−−−+−−−−−−−+
  | 19          |     5 |
  | 20          |    19 |
  | 22          |     8 |
  | 23          |    10 |
  | 24          |     5 |
  | 26          |     4 |
  +−−−−−−−−−−−−−+−−−−−−−+
  </pre>
  
  <p>But ideally I want:</p>
  
  <pre>
  +−−−−−−−−−−−−+−−−−−−−+
  | 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 |
  +−−−−−−−−−−−−+−−−−−−−+
  </pre>
  
  <p>How can I do that?</p>
  
  <hr>
  
  <p>Set up information for testing:</p>
  
  <pre><code>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
  </code></pre>