Solved

sql sERVER PARSE DATA BY HOURS AND COLUMNS

Posted on 2016-08-26
2
50 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:
Scott Pletcher earned 500 total points
ID: 41771994
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
ID: 41772003
Perfect!  Thanks
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I have a large data set and a SSIS package. How can I load this file in multi threading?
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

685 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