none
Hourly Data

    Question

  • Hi,

    I am trying to get total transactions for Cashiers on an hourly basis from my SQL Database. All the data is in the DB, but I have never ran a query that Counts transaction numbers per hour. I can get the total transactions per Cashier for the day, but not per hour.

    Does someone have an answer for me?

    Thanks

    Lawrence

    Tuesday, March 06, 2007 1:34 PM

Answers

  • Assuming that you have a DATETIME column that indicates both the date and time that the row was inserted then you can use the example below as a template. The query will return a rowcount for each hour of the current day in which a row was inserted.

    Chris

     

    SELECT DATEPART(HOUR, MyTable.MyDateField) AS Hour, COUNT(*) AS [RowCount]

    FROM MyTable

    WHERE MyTable.MyDateField >= CAST(CONVERT(VARCHAR(11), GETDATE(), 106) AS DATETIME)

    GROUP BY DATEPART(HOUR, MyTable.MyDateField)

     

    Tuesday, March 06, 2007 1:43 PM

All replies

  • Assuming that you have a DATETIME column that indicates both the date and time that the row was inserted then you can use the example below as a template. The query will return a rowcount for each hour of the current day in which a row was inserted.

    Chris

     

    SELECT DATEPART(HOUR, MyTable.MyDateField) AS Hour, COUNT(*) AS [RowCount]

    FROM MyTable

    WHERE MyTable.MyDateField >= CAST(CONVERT(VARCHAR(11), GETDATE(), 106) AS DATETIME)

    GROUP BY DATEPART(HOUR, MyTable.MyDateField)

     

    Tuesday, March 06, 2007 1:43 PM
  • Thanks Chris,

    I modified as needed and it worked perfect.

    Tuesday, March 06, 2007 1:49 PM
  • Chris,

    I was trying this query on a Weekly basis, but that did not work. How do I get it to work over a long period of time?

    Thanks Chris

    Wednesday, March 07, 2007 3:03 PM
  • Would you expect to see the results broken down by day, or would you want to see the total number of rows for the 3 o'clock to 4 o'clock period (for example) for all days represented by a single row?

    e.g.

    Either:

    Day 1, Hour 3, 564

    Day 2, Hour 3, 989

    Day 3, Hour 3, 43

    etc...

    or a single row for hour 3 for all three days:

    Hour 3, 1596

     

    Chris

    Wednesday, March 07, 2007 3:51 PM
  • Chris,

    What I currently have is:

    Cashier A: Hour :9         Total Transactions:  32

                                  10                                          50

                                  11                                          63     And so on. It would be nice if I could do this as follows:

    Day 1:             Hour :9         Total Transactions:  32

                                  10                                          50

                                  11                                          63    

    Day 2:           Hour :9         Total Transactions:  32

                                  10                                          50

                                  11                                          63    

    Only if it is possible. I am using this in a crystal report, so I can group inside the report, but don't know how to get the data over a longer period than 1 day.

    Thanks for the help Chris.

    • Proposed as answer by STOL3N Thursday, August 29, 2013 3:26 PM
    • Unproposed as answer by STOL3N Thursday, August 29, 2013 3:26 PM
    Wednesday, March 07, 2007 4:58 PM
  • You can simply extend the GROUP BY and SELECT lists to include the year, month and day - see the example below. @MyDate is the earliest date on which you wish to report.

    Chris

     

    DECLARE @MyDate DATETIME

    SET @MyDate = GETDATE()

    SELECT DATEPART(YEAR, MyTable.MyDateField) AS [Year],

    DATEPART(MONTH, MyTable.MyDateField) AS [Month],

    DATEPART(DAY, MyTable.MyDateField) AS [Day],

    DATEPART(HOUR, MyTable.MyDateField) AS [Hour],

    COUNT(*) AS [RowCount]

    FROM MyTable

    WHERE MyTable.MyDateField >= CAST(CONVERT(VARCHAR(11), @MyDate, 106) AS DATETIME)

    GROUP BY DATEPART(YEAR, MyTable.MyDateField),

    DATEPART(MONTH, MyTable.MyDateField),

    DATEPART(DAY, MyTable.MyDateField),

    DATEPART(HOUR, MyTable.MyDateField)

    ORDER BY 1, 2, 3, 4

    Wednesday, March 07, 2007 5:33 PM
  • This is great! Thanks Chris!! This was very helpfull!
    Wednesday, March 07, 2007 5:44 PM