title: Generate a calendar table with hours of the day using SQL Server 2012
body: <p><strong>Problem Statement:</strong> 
  I work for a fire department and I am working on statistical analysis of my data. One issue is to generate the number of calls for service for every hour of every day for a calendar year. I need a table that can be joined to the fire incidents that has every day of the year and every hour of each day. What I am hoping for is the following (using military time)</p>
  
  <p>1 January 2017 00:00:00</p>
  
  <p>1 January 2017 00:00:00</p>
  
  <p>1 January 2017 01:00:00</p>
  
  <p>1 January 2017 02:00:00</p>
  
  <p>1 January 2017 03:00:00</p>
  
  <p>1 January 2017 04:00:00</p>
  
  <p>1 January 2017 05:00:00</p>
  
  <p>1 January 2017 06:00:00</p>
  
  <p>1 January 2017 07:00:00</p>
  
  <p>1 January 2017 08:00:00</p>
  
  <p>etc until the end of the year</p>
  
  <p>31 December 2017 21:00:00</p>
  
  <p>31 December 2017 22:00:00</p>
  
  <p>31 December 2017 23:00:00</p>
  
  <p>end of year</p>
  
  <p>this table will allow me to join to the fire incidents table and I will be able to statistically calculate the number of incidents for each hour of the day and for each day of the year. A calculated table is necessary because the fire incidents table has gaps in it. For example; On january 1st at 0100 hours and 0200 hours and 0300 hours no emergency calls came in. Therefore I cannot make a calculation using the fire incidents table because there is no data for when no calls come in. The fire incidents table with gaps looks like:</p>
  
  <p>TimeInterval, IncidentAddress</p>
  
  <p>1 january 2017 00:00:00,    123 Elm Street</p>
  
  <p>1 January 2017 04:00:00,    456 Oak Street</p>
  
  <p>1 January 2017 05:00:00,    789 Maple Street</p>
  
  <p>(Notice there are no fire calls for the hours of 0100, 0200 and 0300. Those are the gaps.)
  Because there are gaps in the data where zeros should be the calculated averages necessary for a Poisson distribution are missing. The averages are incorrect.</p>
  
  <p><strong>Desired Output:</strong>
  My goal is to have a calendar with an hours of day table to join to my fire incidents so my results set returns. Here is a rough draft of a query that returns every row from the calendar table and rows from the fire incidents table if there is matching value.</p>
  
  <pre><code>SELECT
    TimeInterval
  , COUNT(Incidents) AS [CountOfIncidents] /*this should probably be a     COALESCE statement*/
  FROM CalendarTable /*all rows from the calendar with hours and rows with data from FireIncidents*/
  LEFT OUTER JOIN FireIncidents ON CalendarTable.timeInterval = FireIncidents.TimeInterval
  GROUP BY TimeInterval 
  </code></pre>
  
  <p>Query would return what I am hoping to achieve:</p>
  
  <p>TimeInterval,  CountOfIncidents</p>
  
  <p>1 january 2017 00:00:00, 5</p>
  
  <p>1 January 2017 01:00:00, 0</p>
  
  <p>1 January 2017 02:00:00, 0</p>
  
  <p>1 January 2017 03:00:00, 0</p>
  
  <p>1 January 2017 04:00:00, 2</p>
  
  <p>1 January 2017 05:00:00, 1</p>
  
  <p>(Notice the hours of 0100, 0200 and 0300 have zero number of calls. This is what I want! Now I can create a histogram showing how many hours had zero calls. Or I can calculate an average value that takes into account zero calls for parts of the day.)</p>
  
  <p><strong>What I have tried:</strong>
  I have tried the following but I cannot figure out how to create a table out of this and how to make it a finished product as you can see below in the Question paragraph.</p>
  
  <pre><code>DECLARE @DayOfYearNumber INT
  DECLARE @HourNumber INT
  
  SET @DayOfYearNumber = 1
  SET @HourNumber = 0
  PRINT 'Year' + ', ' + 'CalendarDayOfYear' + ', ' + 'HourOfDay'
  WHILE @DayOfYearNumber &lt; 366
  BEGIN
  SET @HourNumber = 0
  WHILE @HourNumber &lt; 24
  BEGIN PRINT '2017' + ', ' + CONVERT(VARCHAR, @DayOfYearNumber) + '  ' +     CONVERT(VARCHAR, @HourNumber)
  SET @HourNumber = @HourNumber + 1
  END
  SET @DayOfYearNumber = @DayOfYearNumber + 1
  END
  </code></pre>
  
  <p><strong>Question:</strong></p>
  
  <p>How do I generate a calendar table in SQL Server 2012 that will have every day of the year and every hour of each day. My example again</p>
  
  <p>1 January 2017 00:00:00</p>
  
  <p>1 January 2017 01:00:00</p>
  
  <p>1 January 2017 02:00:00</p>
  
  <p>1 January 2017 03:00:00</p>
  
  <p>1 January 2017 04:00:00</p>
  
  <p>1 January 2017 05:00:00</p>
  
  <p>1 January 2017 06:00:00</p>
  
  <p>1 January 2017 07:00:00</p>
  
  <p>1 January 2017 08:00:00</p>
  
  <p>etc until the end of the year</p>
  
  <p>31 December 2017 21:00:00</p>
  
  <p>31 December 2017 22:00:00</p>
  
  <p>31 December 2017 23:00:00</p>
  
  <p>end of year</p>