Hi all,
I need a help in MS SQL Express query. I have a table named "tblTotalCust" and the fields are:
[Date] date, [ServedTime] date, [TotalCustomer] int.
Example data:
[Date] [ServedTime] [TotalCustomer]
8/9/2008 12:00:00AM 8/9/2008 9:23:00AM 12
8/9/2008 12:00:00AM 8/9/2008 9:27:00 AM 7
8/9/2008 12:00:00AM 8/9/2008 9:37:00AM 9
8/9/2008 12:00:00AM 8/9/2008 10:45:00AM 25
8/9/2008 12:00:00AM 8/9/2008 10:57:00AM 11
8/9/2008 12:00:00AM 8/9/2008 11:32:00AM 6
In MS Access, my query is :
SELECT Date, TimeSerial(0, ((HOUR(ServedTime) * 60 + MINUTE(ServedTime))\30)*30,0) AS TimeOfDay, SUM(TotalCustomer) AS TotalCustomer GROUP BY Date, TimeSerial(0,
((HOUR(ServedTime) * 60 + MINUTE(ServedTime))\30)*30,0)
This will produce a sum TotalCustomer in each 30 minute of ServedTime as below:
[Date] [TimeOfDay] [TotalCustomer]
8/9/2008 12:00:00AM 8/9/2008 9:00:00AM 19
8/9/2008 12:00:00AM 8/9/2008 9:30:00AM 9
8/9/2008 12:00:00AM 8/9/2008 10:30:00AM 36
8/9/2008 12:00:00AM 8/9/2008 11:30:00AM 6
But how do i write this query in MS SQL Express? Please help. Thanks
