What SQL query to use for hourly chart?

Question

SQL

Tell me how to correctly compose a SQL query to build a graph by the hour.
I am currently using this query (MSSQL)

SELECT
COUNT(DISTINCT orderT.user) as Orders,
datepart(hh, orderT.date) as Hour
from orderT
where Operation = 'newOrder'
and orderT.date >= Convert(datetime, '2014-11-06 00:00:00') 
and orderT.date < Convert(datetime, '2014-11-06 23:59:59')
GROUP BY datepart(hh, orderT.date)

The result of such a query is a table (orders and hour number):

Orders : Hour
100 : 0
105 : 1
115 : 2
123 : 3
144 : 4
... итд за все 24 часа.

In this case, everything works fine until such hours appear for which there is no data at all. And then the information for this hour completely falls out of the table, which is critical for the chart.
Tell me how to make a request, so that for the hour for which there is no data, the
Base would give the hour number and "0" as a result!

Answer 1

The simplest thing is not to torture the base, but initially create an array of 24 elements, initialize it with zeros and then fill it from the base using the hour number as an index.
Well, or do

UNION (SELECT 0 as Orders, 0 as Hours)
UNION (SELECT 0 as Orders, 1 as Hours)
...
UNION (SELECT 0 as Orders, 23 as Hours)

I don't know how exactly it will be written in MSSQL

Answer 2

If the SQL server version is 2008 or older, then you can do this:

SELECT
    COUNT(DISTINCT [orderT].[user]) as [Orders]
    , [Hours].[Hour]
FROM
    (
        VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12), (13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23)
    ) AS [Hours]([Hour])

LEFT OUTER JOIN
    [orderT]
ON
   datepart(hh, [orderT].[date]) = [Hours].[Hour]
WHERE
    [orderT].[Operation] LIKE 'newOrder'
    AND [orderT].[date] >= Convert(datetime, '2014-11-06 00:00:00') 
    AND [orderT].date < Convert(datetime, '2014-11-06 23:59:59')
GROUP BY
    [Hours].[Hour]