sql sERVER PARSE DATA BY HOURS AND COLUMNS

Posted on 2016-08-26

1 solution

Medium Priority

75 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:Larry Brister

2 Comments

LVL 70

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:Larry Brister

ID: 417720032016-08-26

Perfect! Thanks

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment Already a member? Login.

Installing SQL Server vNext on Linux: Part 1 (Prerequisites)

Article by: Yashwant

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.

How to Recover Deleted SQL Data with the Help of Best SQL Recovery Software

Article by: Bharat

In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…

SQL - The SELECT Statement

Video by: Zia

Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

Microsoft SQL Server

Repair Corrupt SQL Database with Stellar Phoenix SQL Database Repair Software

Video by: Bharat

SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…