Wednesday, January 19, 2011

Displaying data by hour

I needed to display record count hour by hour for a 24 hour period, displaying just the hour and record count for that hour, without the date part but ordered not from 0 to 23 but from current hour, i.e. if it is currently 9:28am, then the data should be displayed from 9am today up to 8am tomorrow. Here is what I came up with:

SELECT CONVERT(VARCHAR(8),MyDateField,101) + ' ' + CONVERT(VARCHAR(2),[MyDateField],108) as TDate,
DATEPART(hh,MyDateField) AS Hour,
COUNT(*) AS NumberOfRecordsPerHour
FROM MyTable
WHERE DATEDIFF(hh, MyDateField, GETDATE())<= 24
GROUP BY CONVERT(VARCHAR(8),MyDateField,101) + ' ' + CONVERT(VARCHAR(2),MyDateField,108), DATEPART(hh,MyDateField)