Solved

sql sERVER PARSE DATA BY HOURS AND COLUMNS

Posted on 2016-08-26

1 solution

Medium Priority

57 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

Desired Results

Comment

Question by:lrbrister

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

2 Comments

LVL 69

Accepted Solution

by:Scott Pletcher

Scott Pletcher earned 2000 total points

ID: 417719942016-08-26

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)

Author Closing Comment

by:lrbrister

ID: 417720032016-08-26

Perfect! Thanks

Featured Post

Question has a verified solution.

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

Things To Know About Local Load Balancing

Article by: Tony

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.

Shutdown and Startup Procedures for Citrix XenDesktop Environment (7.x)

Article by: Paul

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …

Dealing with Dates: Data Types and String Conversions (SQL Server)

Video by: Steve

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.