How to list counts hour by hour

  • 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

    call per hour<b></b><i></i><u></u>

    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

  • 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 1 year, 10 months ago by  pietlinden.
    • This reply was modified 1 year, 10 months ago by  pietlinden.
  • thank you

    This really gives me some good ideas.

     

  • 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

  • thanks for the post it looks like a great article.

  • 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.

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

    1. What do you want done for hours that have no entries in the table because there were no calls?
    2. What do you want done for multiple days?  What should THAT format look like?
    3. 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • 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.

  • Thanks that is a great idea too.

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

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