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!