generate table of months, dates, hours. cross reference with actual useage data to avoid blank spaces in graphs.

gistfile1.sql

# i dont remember if i wrote this, or took it from somewhere

select 
  month_number,
	day_number,
	hour_number,
	isnull(d.data,0)
FROM
(
		--generated calendar
		SELECT 
			MONTH(months.date_val) AS month_number,
			DAY(days.date_val) AS day_number,
			datepart(hh, hours.date_val) as hour_number
		FROM 
			( 	SELECT DATEADD(dd, number, '2012-01-01') AS date_val
				FROM master.dbo.spt_values
				WHERE type = 'P'
				AND number BETWEEN 0 AND 30
			) days 
			
		JOIN
			(	SELECT DATEADD(mm, number, '2012-01-01') AS date_val
				FROM master.dbo.spt_values
				WHERE type = 'P'
				AND number BETWEEN 0 AND 11
			) months
			on 1=1
			
		JOIN
			(	SELECT DATEADD(hh, number, '2012-01-01') AS date_val
				FROM master.dbo.spt_values
				WHERE type = 'P'
				AND number BETWEEN 1 AND 24
			) hours
			on 1=1

) calendar


 FULL JOIN 
 
 (
		--this is our actual date data. generated calenda
		SELECT [Data], [Month], [Day], [Hour] from
			(	select COUNT(*) as [Data], [Month], [Day], [Hour] from
				(
					select 
						DATEPART(year, tlogin) as [Year],
						DATEPART(month, tlogin) as [Month],
						datepart(day, tlogin) as [Day],
						DATEPART(hour, tlogin) as [Hour]
					from log
				) a
				where [YEAR] like 2012
				group by [Year], [Month], [Day], [Hour]
			) data
) d 

	on d.Day = day_number
	and d.Month = month_number
	and d.Hour = hour_number

order by month_number, day_number, hour_number