Generate a calendar table with hours of the day us

Problem Statement: 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)

1 January 2017 00:00:00

1 January 2017 01:00:00

1 January 2017 02:00:00

1 January 2017 03:00:00

1 January 2017 04:00:00

1 January 2017 05:00:00

1 January 2017 06:00:00

1 January 2017 07:00:00

1 January 2017 08:00:00

etc until the end of the year

31 December 2017 21:00:00

31 December 2017 22:00:00

31 December 2017 23:00:00

end of year

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:

TimeInterval, IncidentAddress

1 january 2017 00:00:00, 123 Elm Street

1 January 2017 04:00:00, 456 Oak Street

1 January 2017 05:00:00, 789 Maple Street

(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.

Desired Output: 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.

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

Query would return what I am hoping to achieve:

TimeInterval, CountOfIncidents

1 january 2017 00:00:00, 5

1 January 2017 01:00:00, 0

1 January 2017 02:00:00, 0

1 January 2017 03:00:00, 0

1 January 2017 04:00:00, 2

1 January 2017 05:00:00, 1

(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.)

What I have tried: 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.

DECLARE @DayOfYearNumber INT
DECLARE @HourNumber INT

SET @DayOfYearNumber = 1
SET @HourNumber = 0
PRINT 'Year' + ', ' + 'CalendarDayOfYear' + ', ' + 'HourOfDay'
WHILE @DayOfYearNumber < 366
BEGIN
SET @HourNumber = 0
WHILE @HourNumber < 24
BEGIN PRINT '2017' + ', ' + CONVERT(VARCHAR, @DayOfYearNumber) + '  ' +     CONVERT(VARCHAR, @HourNumber)
SET @HourNumber = @HourNumber + 1
END
SET @DayOfYearNumber = @DayOfYearNumber + 1
END

Question:

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

1 January 2017 00:00:00

1 January 2017 01:00:00

1 January 2017 02:00:00

1 January 2017 03:00:00

1 January 2017 04:00:00

1 January 2017 05:00:00

1 January 2017 06:00:00

1 January 2017 07:00:00

1 January 2017 08:00:00

etc until the end of the year

31 December 2017 21:00:00

31 December 2017 22:00:00

31 December 2017 23:00:00

end of year

回答1:

A simple method uses recursion:

with d as (
      select cast('2017-01-01' as datetime) as dte
      union all
      select dateadd(hour, 1, dte)
      from d
      where dateadd(hour, 1, dte) < '2018-01-01'
     )
select d.*
from d
option (maxrecursion 0);

Although recursion is surprisingly fast, if you are going to be needing this multiple times, you might want to consider have a numbers table around or storing this in a temporary or permanent table.

回答2:

A alternative method to using a rCTE is a Tally Table, as it's not RBAR:

DECLARE @TopDate date = '20550101';

WITH N AS(
    SELECT *
    FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) V(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I
    FROM N N1
         CROSS JOIN N N2
         CROSS JOIN N N3
         CROSS JOIN N N4
         CROSS JOIN N N5
         CROSS JOIN N N6)
SELECT DATEADD(HOUR, I, '20170101') AS DateValue
FROM Tally
WHERE DATEADD(HOUR, I, '20170101') < @TopDate;

回答3:

You could achieve it using single query. All you need is tally(number) table:

WITH tally(n) AS (
  SELECT ROW_NUMBER() OVER(ORDER BY 1/0)-1
  FROM master..spt_values s1, master..spt_values s2, master..spt_values s3
)
-- INSERT INTO calendar(col_name)
SELECT DATEADD(HOUR,n,'20170101') AS d
FROM tally
WHERE DATEADD(HOUR,n,'20170101') <= '20180101'

Rextester Demo