title: SQL Query to return 24 hour, hourly count even when no values exist?
body: <p>I've written a query that groups the number of rows per hour, based on a given date range.</p>
  
  <pre><code>SELECT CONVERT(VARCHAR(8),TransactionTime,101) + ' ' + CONVERT(VARCHAR(2),TransactionTime,108) as TDate, 
      COUNT(TransactionID) AS TotalHourlyTransactions
      FROM MyTransactions WITH (NOLOCK)
      WHERE TransactionTime BETWEEN CAST(@StartDate AS SMALLDATETIME) AND CAST(@EndDate AS SMALLDATETIME)
      AND TerminalId = @TerminalID
      GROUP BY CONVERT(VARCHAR(8),TransactionTime,101) + ' ' + CONVERT(VARCHAR(2),TransactionTime,108)
      ORDER BY TDate ASC
  </code></pre>
  
  <p>Which displays something like this:</p>
  
  <pre><code>02/11/20 07 4
  02/11/20 10 1
  02/11/20 12 4
  02/11/20 13 1
  02/11/20 14 2
  02/11/20 16 3
  </code></pre>
  
  <p>Giving the number of transactions and the given hour of the day.</p>
  
  <p>How can I display all hours of the day - from 0 to 23, and show 0 for those which have no values?</p>
  
  <p>Thanks.</p>
  
  <h2>UPDATE</h2>
  
  <p>Using the tvf below works for me for one day, however I'm not sure how to make it work for a date range.</p>
  
  <p>Using the temp table of 24 hours:</p>
  
  <pre><code> -- temp table to store hours of the day    
   DECLARE @tmp_Hours TABLE ( WhichHour SMALLINT )
  
   DECLARE @counter SMALLINT
   SET @counter = -1
   WHILE @counter &lt; 23 
      BEGIN
          SET @counter = @counter + 1
        --print 
          INSERT  INTO @tmp_Hours
                  ( WhichHour )
          VALUES  ( @counter )
      END 
  
      SELECT MIN(CONVERT(VARCHAR(10),[dbo].[TerminalTransactions].[TransactionTime],101)) AS TDate, [@tmp_Hours].[WhichHour], CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108) AS TheHour,
          COUNT([dbo].[TerminalTransactions].[TransactionId]) AS TotalTransactions, 
          ISNULL(SUM([dbo].[TerminalTransactions].[TransactionAmount]), 0) AS TransactionSum
      FROM [dbo].[TerminalTransactions] RIGHT JOIN @tmp_Hours ON [@tmp_Hours].[WhichHour] = CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108) 
      GROUP BY [@tmp_Hours].[WhichHour], CONVERT(VARCHAR(2),[dbo].[TerminalTransactions].[TransactionTime],108),  COALESCE([dbo].[TerminalTransactions].[TransactionAmount], 0)
  </code></pre>
  
  <p>Gives me a result of:</p>
  
  <pre><code>TDate      WhichHour TheHour TotalTransactions TransactionSum
  ---------- --------- ------- ----------------- ---------------------
  02/16/2010 0         00      4                 40.00
  NULL       1         NULL    0                 0.00
  02/14/2010 2         02      1                 10.00
  NULL       3         NULL    0                 0.00
  02/14/2010 4         04      28                280.00
  02/14/2010 5         05      11                110.00
  NULL       6         NULL    0                 0.00
  02/11/2010 7         07      4                 40.00
  NULL       8         NULL    0                 0.00
  02/24/2010 9         09      2                 20.00
  </code></pre>
  
  <p>So how can I get this to group properly?</p>
  
  <p>The other issue is that for some days there will be no transactions, and these days also need to appear.</p>
  
  <p>Thanks.</p>