Hourly Data

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

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)

|||

Thanks Chris,

I modified as needed and it worked perfect.

|||

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

|||

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

|||

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.

|||

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

|||This is great! Thanks Chris!! This was very helpfull!