No need for uncle Google in this case, spt_values is quite enough, unless you are querying some large data warehouse.

However, I agree that GetNums is a cool trick to generate large order numbers.

I was thinking of the following solution:

I first add one computed column in the fact table (I assume that it is ok to do that) in order to keep the query SARGable. The new column will have the sales datetime rounded up to hour percision.

ALTER TABLE #SalesTestData

ADD SaleTimeHour AS CONVERT(DATETIME, CONVERT(VARCHAR,SaleTime,112)+' '+CAST(DATEPART(HOUR,SaleTime) AS VARCHAR(2))+':00:00.000',112) PERSISTED

The report will then be formed by this single query:

DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate='2011-01-01 00:00:00.000'

SET @EndDate='2011-12-01 00:00:00.000'

SELECT CAST(AllDates.ReportDayHour AS DATE) AS ReportDay, CAST(AllDates.ReportDayHour AS TIME) AS ReportHour,

ISNULL(SUM(s.SaleQty),0) AS TotalSaleQty

FROM (

SELECT DATEADD(hh,h.number,DATEADD(dd,d.number,DATEADD(mm,m.number,@StartDate))) AS ReportDayHour

FROM master..spt_values m

CROSS JOIN master..spt_values d

CROSS JOIN master..spt_values h

WHEREm.type='p' AND m.number BETWEEN 0 AND DATEDIFF(mm,@StartDate,@EndDate)

AND d.type='p' AND d.number BETWEEN 0 AND 30

AND h.type='p' AND h.number BETWEEN 0 AND 23

) AS AllDates

LEFT JOIN #SalesTestData s ON s.SaleTimeHour=AllDates.ReportDayHour

GROUP BY AllDates.ReportDayHour

ORDER BY AllDates.ReportDayHour

The parameters are the start and end moments of the reports, as any manager would want 🙂