Tuesday, 22 September 2015

Get Hourly Created records count for a day from a SQL Table

SELECT STUFF(RIGHT(CONVERT(VARCHAR,Createddate,0),7),3,3,' ') + ' - ' +
STUFF(RIGHT(CONVERT(VARCHAR,DATEADD(HOUR,1,Createddate),0),7),3,3,' ') AS HOUR, COUNT(*) AS COUNT
FROM tableName
WHERE Createddate BETWEEN CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE()))) AND CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())) + 1)
GROUP BY STUFF(RIGHT(CONVERT(VARCHAR,Createddate,0),7),3,3,' ') + ' - ' +
STUFF(RIGHT(CONVERT(VARCHAR,DATEADD(HOUR,1,Createddate),0),7),3,3,' ')
ORDER BY 1