[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

sql sERVER PARSE DATA BY HOURS AND COLUMNS

Posted on 2016-08-26
2
Medium Priority
?
61 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
[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
2 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

656 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