Solved

sql sERVER PARSE DATA BY HOURS AND COLUMNS

Posted on 2016-08-26
2
29 Views
Last Modified: 2016-08-26
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
Results Now
Desired Results
Results Preferred
0
Comment
Question by:lrbrister
2 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
Comment Utility
DECLARE @SQL varchar(MAX)
SET @SQL = 'SELECT  ''Summary Records'',
      SUM(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 0 THEN 1 ELSE 0 END) AS CurHour,
    SUM(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 1 THEN 1 ELSE 0 END) AS ''['  +
        LTRIM(STUFF(RIGHT(CONVERT(varchar(30), DATEADD (HOUR, -1, GETDATE()), 0), 7), 3, 3, '')) + ']'',
    SUM(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 2 THEN 1 ELSE 0 END) AS ''['  +
        LTRIM(STUFF(RIGHT(CONVERT(varchar(30), DATEADD (HOUR, -2, GETDATE()), 0), 7), 3, 3, '')) + ']'',
    SUM(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 3 THEN 1 ELSE 0 END) AS ''['  +
        LTRIM(STUFF(RIGHT(CONVERT(varchar(30), DATEADD (HOUR, -3, GETDATE()), 0), 7), 3, 3, '')) + ']'',
    SUM(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 4 THEN 1 ELSE 0 END) AS ''['  +
        LTRIM(STUFF(RIGHT(CONVERT(varchar(30), DATEADD (HOUR, -4, GETDATE()), 0), 7), 3, 3, '')) + ']'',
    SUM(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 5 THEN 1 ELSE 0 END) AS ''['  +
        LTRIM(STUFF(RIGHT(CONVERT(varchar(30), DATEADD (HOUR, -5, GETDATE()), 0), 7), 3, 3, '')) + ']'',
    SUM(CASE WHEN DATEDIFF(HH, DateAdded, getdate()) = 6 THEN 1 ELSE 0 END) AS ''['  +
        LTRIM(STUFF(RIGHT(CONVERT(varchar(30), DATEADD (HOUR, -6, GETDATE()), 0), 7), 3, 3, '')) + ']'',
    SUM(1) AS TOTAL
    FROM SendGridSummary
WHERE DateAdded >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AND
              DateAdded < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)'
--PRINT @sql
EXEC(@SQL)
0
 

Author Closing Comment

by:lrbrister
Comment Utility
Perfect!  Thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now