And although it's a tiny bit of a pain, it's well worth creating an 5-minute interval table, as I suggested.
Say you create a table named
FiveMinuteIntervals and it has a *single* field named
IntervalStart that is
DATETIME datatype.
If you just enter the first hour of 5 minute intervals into the table (that is 8:00AM, 8:05AM, ..., 8:55AM ... OR if you want the intervals to start on the 1 minute mark then 8:01AM, 8:06AM, ..., 8:56AM) then you can use a simple query to create the other hours.
Something like this:
INSERT INTO FiveMinuteIntervals
SELECT TOP 12 DATEADD('h',1,IntervalStart)
FROM FiveMinuteIntervals
ORDER BY IntervalStart DESC
And then you just execute that stored query 11 times and you should have 5 minute intervals from 8 am to 8 pm.
*NOW* you can just do
Code:
SELECT FMI.IntervalStart, COUNT(T.TransactionTime) AS numberOfCalls
FROM FiveMinuteIntervals AS FMI LEFT JOIN yourMainTable AS T
ON ( DATEDIFF( 'n', #8:00:00 AM#, FMI.IntervalStart )
= 5 * ( DATEDIFF( 'n', #8:00:00 AM#, T.TransactionTime ) \ 5 )
)
GROUP BY FMI.IntervalStart
ORDER BY FMI.IntervalStart
If your intervals start at 8:01, 8:06, etc. (instead of 8:00, 8:05, as I have assumed) then you would use
#8:01:00 AM#
in both places there.
