Get all days between two dates with all day hours in SQL Server

Question

I have to generate a result set of a SQL query which should match the following, but let me explain both inputs and outputs:

I have a table named Orders and this table has some orders in some days at some hours, then, I have been requested to provide a result-set which should get all days between two dates (i.e. 2017-10-01 and 2017-10-07), with all 24 hours for each day, even if that day or that hour had no orders, but it should be appeared with 0 value.

+------------+------+-------------+
| Day        | Hour | TotalOrders |
+------------+------+-------------+
| 2017-10-01 |   0  |      0      |
+------------+------+-------------+
| 2017-10-01 |   1  |      3      |
+------------+------+-------------+
| 2017-10-01 |   2  |      4      |
+------------+------+-------------+
| 2017-10-01 |   3  |      0      |
+------------+------+-------------+
| 2017-10-01 |   4  |      7      |
+------------+------+-------------+
| 2017-10-01 |   5  |      0      |
+------------+------+-------------+
| 2017-10-01 |   6  |      0      |
+------------+------+-------------+
| 2017-10-01 |   7  |      9      |
+------------+------+-------------+
| 2017-10-01 |   8  |      0      |
+------------+------+-------------+
| 2017-10-01 |   9  |      0      |
+------------+------+-------------+
| 2017-10-01 |  10  |      0      |
+------------+------+-------------+
| 2017-10-01 |  11  |      0      |
+------------+------+-------------+
| 2017-10-01 |  12  |      0      |
+------------+------+-------------+
| 2017-10-01 |  13  |      0      |
+------------+------+-------------+
| 2017-10-01 |  14  |      0      |
+------------+------+-------------+
| 2017-10-01 |  15  |      0      |
+------------+------+-------------+
| 2017-10-01 |  16  |      0      |
+------------+------+-------------+
| 2017-10-01 |  17  |      0      |
+------------+------+-------------+
| 2017-10-01 |  18  |      0      |
+------------+------+-------------+
| 2017-10-01 |  19  |      0      |
+------------+------+-------------+
| 2017-10-01 |  20  |      0      |
+------------+------+-------------+
| 2017-10-01 |  21  |      0      |
+------------+------+-------------+
| 2017-10-01 |  22  |      0      |
+------------+------+-------------+
| 2017-10-01 |  23  |      0      |
+------------+------+-------------+
| 2017-10-02 |   0  |      0      |
+------------+------+-------------+
| 2017-10-02 |   1  |      0      |
+------------+------+-------------+
| 2017-10-02 |   2  |      0      |
+------------+------+-------------+
| 2017-10-02 |   3  |      0      |
+------------+------+-------------+
| 2017-10-02 |   4  |      0      |
+------------+------+-------------+
| 2017-10-02 |   5  |      0      |
+------------+------+-------------+
| 2017-10-02 |   6  |      0      |
+------------+------+-------------+
| 2017-10-02 |   7  |      0      |
+------------+------+-------------+
| and so on ..................    |
+------------+------+-------------+

So, the above result set should contain every day between the given two dates, and each day should have all 24 hours, irrespective off that day had orders and the same for hour (either it had orders or not)

Answer 1

The simplest way is to just use a temporary table or table variable to fill the desired result set, and then count the number of Orders for each row.

declare @Date1 date = '2017-10-01';
declare @Date2 date = '2017-10-07';
declare @Hour int;
declare @Period table (Day Date, Hour Time);

while @Date1 <= @Date2
begin
  set @Hour = 0;
  while @Hour < 24
  begin
    insert into @Period (Day, Hour) values (@Date1, TimeFromParts(@Hour,0,0,0,0));
    set @Hour = @Hour + 1; 
  end
  set @Date1 = DateAdd(Day, 1, @Date1);
end

select Day, Hour, 
       (select count(*) 
        from Orders
        where Orders.Day = Period.Day and Orders.Hour = Period.Hour) as TotalOrders
from @Period as Period;

Answer 2

I did it using a nested CTE:

DECLARE @MinDate DATE = '20171001',
    @MaxDate DATE = '20171006';

 ;WITH INNER_CTE as(
SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
    Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
    CROSS JOIN sys.all_objects b) ,

OUTER_CTE as (
select * from INNER_CTE 
cross apply (
SELECT TOP (24) n = ROW_NUMBER()  OVER (ORDER BY [object_id]) -1
FROM sys.all_objects ORDER BY n)) t4
 )

  select t1.Date, t1.n [Hour], ISNULL(t2.TotalORders,0) TotalOrders from 
  OUTER_CTE t1
 LEFT JOIN orders t2 on t1.Date = t2.[Day] and t1.n = t2.[Hour]

Good Reading about generating sequences using a query here: https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1

Answer 3

I prefer to do this with a tally table instead of using loops. The performance is much better. I keep a tally on my system as a view like this.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

Now that we have our tally table we can use some basic math to get the desired output. Something along these lines.

declare @Date1 datetime = '2017-10-01';
declare @Date2 datetime = '2017-10-07';

select Day = convert(date, DATEADD(hour, t.N, @Date1))
    , Hour = t.N - 1
    , TotalOrders = COUNT(o.OrderID)
from cteTally t
left join Orders o on o.OrderDate = DATEADD(hour, t.N, @Date1)
where t.N <= DATEDIFF(hour, @Date1, @Date2)
group by DATEDIFF(hour, @Date1, @Date2)
    , t.N