Link to home
Create AccountLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

sql sERVER PARSE DATA BY HOURS AND COLUMNS

My code below does produce results.
I am kind of "brute forcing" it.

I would prefer this to be more "dynamic"
AND
That the column headers reflect AM and PM without massive code

DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SELECT  ''Summary Records'',
	COUNT(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 0 THEN 1 END) AS CurHour,
    COUNT(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 1 THEN 1 END) AS ''['  + CAST(DATEPART (HH , GETDATE()) -1 AS VARCHAR(2)) + ']'',
    COUNT(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 2 THEN 1 END) AS ''['  + CAST(DATEPART (HH , GETDATE()) -2 AS VARCHAR(2)) + ']'',
    COUNT(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 3 THEN 1 END) AS ''['  + CAST(DATEPART (HH , GETDATE()) -3 AS VARCHAR(2)) + ']'',
    COUNT(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 4 THEN 1 END) AS ''['  + CAST(DATEPART (HH , GETDATE()) -4 AS VARCHAR(2)) + ']'',
    COUNT(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 5 THEN 1 END) AS ''['  + CAST(DATEPART (HH , GETDATE()) -5 AS VARCHAR(2)) + ']'',
    COUNT(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 6 THEN 1 END) AS ''['  + CAST(DATEPART (HH , GETDATE()) -6 AS VARCHAR(2)) + ']'',
    COUNT(*) AS TOTAL FROM SendGridSummary
WHERE (DATEDIFF(d, DateAdded, GETDATE()) = 0)'
EXEC(@SQL)

Open in new window


Current results
User generated image
Desired Results
User generated image
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Larry Brister

ASKER

Perfect!  Thanks