title: SQL - SHOW ALL DATES between two dates
body: <p>I have following data saved as dates in my <code>[OccuredAtUtc]</code>  that look like this:</p>
  
  <p>-- <em>Spoiler ALERT: "2017-04-26" and "2017-04-29" are missing.</em></p>
  
  <blockquote>
    <p>Original dates in <code>[OccuredAtUtc]</code>:</p>
  </blockquote>
  
  <pre><code>2017-04-24 12:16:58.5080000
  2017-04-24 18:11:53.3090000
  2017-04-25 18:34:18.3090000
  2017-04-27 20:42:28.8570000
  2017-04-28 21:10:36.7070000
  2016-04-28 10:37:57.5970000
  2016-04-30 10:38:55.7010000
  2016-04-30 10:48:19.0390000
  2016-04-31 10:48:19.2990000
  .
  .
  .
  </code></pre>
  
  <p>And I have this code that returns correctly data from two intervals (previous week). </p>
  
  <pre><code>SELECT 
          [MessageType].[Name] AS [Channel],
          CONVERT(VARCHAR(11), [OccuredAtUtc], 106) AS [Time],
          COUNT(*) AS [Count]
  FROM @table1
          INNER JOIN @table2 ON ... = ...
  WHERE ( [OccuredAtUtc] &gt; '2017-04-24'
          AND [OccuredAtUtc] &lt; '2017-04-30' )
  GROUP BY (CONVERT(VARCHAR(11), [OccuredAtUtc], 106)),
           [MessageType].[Name]
  ORDER BY [Time] ASC
  </code></pre>
  
  <p>But <strong>the output won't show a row of the "26 Apr 2017" and "29 Apr 2017"</strong> because there are not records on these days in my DB.</p>
  
  <p><strong>OLD OUTPUT :</strong>  with missing 26th &amp; 29th Apr.</p>
  
  <pre><code>[Channel]       [Time]          [Count]
  ------------------------------------
  FTP           24 Apr 2017         7
  HTTP          24 Apr 2017         9
  FTP           25 Apr 2017         6
  HTTP          25 Apr 2017         2
  ------MISSING 26 Apr--------
  FTP           27 Apr 2017         56
  HTTP          27 Apr 2017         12
  FTP           28 Apr 2017         5
  ------MISSING 29 Apr--------
  HTTP          28 Apr 2017         17
  FTP           30 Apr 2017         156
  HTTP          30 Apr 2017         19
  </code></pre>
  
  <p>I would like to <strong>show rows WITH THE MISSING DATE even if there was not an incident</strong> saved on this day... </p>
  
  <p>So the new OUTPUT should look like this.</p>
  
  <p><strong>WANTED OUTPUT :</strong></p>
  
  <pre><code>[Channel]       [Time]          [Count]
  ------------------------------------
  FTP             24 Apr 2017        7
  HTTP            24 Apr 2017        9
  FTP             25 Apr 2017        6
  HTTP            25 Apr 2017        2
  0               26 Apr 2017        0  -- here we go
  FTP             27 Apr 2017        56
  HTTP            27 Apr 2017        12
  FTP             28 Apr 2017        5
  HTTP            28 Apr 2017        17
  0               29 Apr 2017        0  -- here we go
  FTP             30 Apr 2017        156
  HTTP            30 Apr 2017        19
  </code></pre>
  
  <p>I know there are answered question like mine and I was trying to remake my code but I failed.</p>
  
  <p>( <a href="https://stackoverflow.com/questions/12890967/show-all-dates-data-between-two-dates-if-no-row-exists-for-particular-date-then">SHOW ALL Dates data between two dates; if no row exists for particular date then show zero in all columns</a> )</p>
  
  <p>( <a href="https://stackoverflow.com/questions/32982372/how-to-generate-all-dates-between-two-dates">How to generate all dates between two dates</a> )</p>