How to list counts hour by hour

Question

How to list counts hour by hour

itmasterw 60042

SSCrazy

Points: 2858
More actions
May 20, 2019 at 6:21 pm

#3645502

Hi,
Below I have a sample of the output, and below that is a sample of how the data is stored in the database.
I am trying to figure out how the queried this to get the calls per hour, I looked online but could not find anything exactly like this maybe ai am sating this wrong.
Can someone give me an idea of how to do this
Thank you

In the database
Date Time Calls
05/17/2019 00:51:39.0000 1
05/17/2019 06:54:34.0000 2
05/17/2019 07:20:35.0000 1
05/17/2019 08:28:26.0000 3

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

SELECT y.EventDate
, y.EventHour
, COUNT(*) AS Freq
FROM
(SELECT x.EventDate,
DATEPART(hour,x.EventTime) AS EventHour
FROM
(SELECT '05/17/2019' AS EventDate, '00:51:39.0000' AS EventTime,     1 AS CallCount
UNION ALL
SELECT '05/17/2019', '06:54:34.0000',     2
UNION ALL
SELECT '05/17/2019', '07:20:35.0000',    1
UNION ALL
SELECT '05/17/2019', '08:28:26.0000',   3) x ) y
GROUP BY EventDate, EventHour;

Oh, the text "9-10 AM" would just be a string... You could join to a table of (Hour, TextSlot) and group that way or just create an expression to generate it in your initial SELECT statement and then group.

This reply was modified 5 years, 6 months ago by pietlinden.
This reply was modified 5 years, 6 months ago by pietlinden.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 2

itmasterw 60042

SSCrazy

Points: 2858

May 21, 2019 at 11:14 am

#3645644

thank you

This really gives me some good ideas.

jonathan.crawford SSCertifiable Points: 6612 More actions · Answer 3

Jeremiah Peschka has an option https://facility9.com/2010/02/rounding-to-the-nearest-x-minutes-the-lazy-way/

-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 4

thanks for the post it looks like a great article.

pietlinden SSC Guru Points: 63503 More actions · Answer 5

If you have a table for the times

CREATE TABLE HoursList (

HourNumber TINYINT PRIMARY KEY

, TimeSpan VARCHAR(10) NOT NULL

);

Then you insert values for each

INSERT INTO HoursList(0, '12 AM-1 AM'),(1,'1 AM - 2 AM') … ;

Then you can just join that to your query and group everything together that way.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 6

No pun intended but let's hold the phone for a minute here. 😉

What do you want done for hours that have no entries in the table because there were no calls?
What do you want done for multiple days? What should THAT format look like?
Are you going to need reports with aggregations for weeks and months as well?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

David Burrows SSC Guru Points: 65142 More actions · Answer 7

pietlinden wrote:

If you have a table for the times CREATE TABLE HoursList ( HourNumber TINYINT PRIMARY KEY , TimeSpan VARCHAR(10) NOT NULL ); Then you insert values for each INSERT INTO HoursList(0, '12 AM-1 AM'),(1,'1 AM - 2 AM') … ; Then you can just join that to your query and group everything together that way.

+1

🙂

Far away is close at hand in the images of elsewhere.
Anon.

itmasterw 60042 SSCrazy Points: 2858 More actions · Answer 8

itmasterw 60042

SSCrazy

Points: 2858

May 22, 2019 at 11:08 am

#3646053

Thanks that is a great idea too.