You just need some sort of Number/Tally table and a LEFT OUTER JOIN. Here is a sample with an inline-tally table:-- Sample Data
DECLARE @Foo TABLE (start_date DATETIME)
INSERT @Foo
VALUES
(SYSDATETIME())
,(DATEADD(HOUR, -2, SYSDATETIME()))
,(SYSDATETIME())
,(SYSDATETIME())
,(SYSDATETIME())
,(DATEADD(HOUR, 3, SYSDATETIME()))
-- Query
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9),
Hudreds (N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Hudreds)
SELECT
Tally.N
,COALESCE(A.Hours, 0) AS Hours
FROM Tally
LEFT OUTER JOIN
(
SELECT
CASE
WHEN CAST(wo.start_date AS TIME) BETWEEN '00:00:00' AND '00:59:59' THEN 0
WHEN CAST(wo.start_date AS TIME) BETWEEN '01:00:00' AND '01:59:59' THEN 1
WHEN CAST(wo.start_date AS TIME) BETWEEN '02:00:00' AND '02:59:59' THEN 2
WHEN CAST(wo.start_date AS TIME) BETWEEN '03:00:00' AND '03:59:59' THEN 3
WHEN CAST(wo.start_date AS TIME) BETWEEN '04:00:00' AND '04:59:59' THEN 4
WHEN CAST(wo.start_date AS TIME) BETWEEN '05:00:00' AND '05:59:59' THEN 5
WHEN CAST(wo.start_date AS TIME) BETWEEN '06:00:00' AND '06:59:59' THEN 6
WHEN CAST(wo.start_date AS TIME) BETWEEN '07:00:00' AND '07:59:59' THEN 7
WHEN CAST(wo.start_date AS TIME) BETWEEN '08:00:00' AND '08:59:59' THEN 8
WHEN CAST(wo.start_date AS TIME) BETWEEN '09:00:00' AND '09:59:59' THEN 9
WHEN CAST(wo.start_date AS TIME) BETWEEN '10:00:00' AND '10:59:59' THEN 10
WHEN CAST(wo.start_date AS TIME) BETWEEN '11:00:00' AND '11:59:59' THEN 11
WHEN CAST(wo.start_date AS TIME) BETWEEN '12:00:00' AND '12:59:59' THEN 12
WHEN CAST(wo.start_date AS TIME) BETWEEN '13:00:00' AND '13:59:59' THEN 13
WHEN CAST(wo.start_date AS TIME) BETWEEN '14:00:00' AND '14:59:59' THEN 14
WHEN CAST(wo.start_date AS TIME) BETWEEN '15:00:00' AND '15:59:59' THEN 15
WHEN CAST(wo.start_date AS TIME) BETWEEN '16:00:00' AND '16:59:59' THEN 16
WHEN CAST(wo.start_date AS TIME) BETWEEN '17:00:00' AND '17:59:59' THEN 17
WHEN CAST(wo.start_date AS TIME) BETWEEN '18:00:00' AND '18:59:59' THEN 18
WHEN CAST(wo.start_date AS TIME) BETWEEN '19:00:00' AND '19:59:59' THEN 19
WHEN CAST(wo.start_date AS TIME) BETWEEN '20:00:00' AND '20:59:59' THEN 20
WHEN CAST(wo.start_date AS TIME) BETWEEN '21:00:00' AND '21:59:59' THEN 21
WHEN CAST(wo.start_date AS TIME) BETWEEN '22:00:00' AND '22:59:59' THEN 22
WHEN CAST(wo.start_date AS TIME) BETWEEN '23:00:00' AND '23:59:59' THEN 23
END AS Hours
FROM
@Foo AS wo
) AS A
ON Tally.N = A.Hours
WHERE
Tally.N <= 24