SQL Query by time interval

Question

So I have used this post as a reference, however I would like to count all the rows based on a 15 minute time period.

Here is what I have so far:

SELECT      DateAdd(minute, DateDiff(minute, 0, [datetime]), 0) as Timestamp, 
            Count(*) as Tasks
FROM        [table]
GROUP BY    DateAdd(minute, DateDiff(minute, 0, [datetime]), 0)
ORDER BY    Timestamp

This is great for getting rows per minute, however I need 15 minutes... So I change:

DateAdd(minute, DateDiff(minute, 0, [datetime]), 0)

to

DateAdd(minute, DateDiff(minute, 0, [datetime]), 15)

however that is just pushing the date 15 days ahead.

Any help is appreciated!

Answer 1

To get 15 minutes, divide by 15 (and then multiply again):

SELECT      DateAdd(minute, 15*(DateDiff(minute, 0, [datetime]) / 15), 0
                   ) as Timestamp, 
            Count(*) as Tasks
FROM        [table]
GROUP BY    (DateDiff(minute, 0, [datetime]) / 15)
ORDER BY    Timestamp;

SQL Server does integer division. If you want to be unambiguous about your intentions, use FLOOR().