You just need some sort of Number/Tally table and a LEFT OUTER JOIN. Here is a sample with an inline-tally table:-- Sample DataDECLARE @Foo TABLE (start_date DATETIME)INSERT @FooVALUES(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 HoursFROM TallyLEFT 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.HoursWHERE Tally.N <= 24