SQL Server 2000 - splitting a query into 15 minute blocks

Question

I have a permanent dataset of time and I want to split it into 15 minute blocks using sql.

I do not want to create a new table in order to be able to do this if I can help.

i.e.
Time, Count
09:15, 1
09:30, 3
09:45, 0
10:00, 2
10:15, 3
.....

Does anyone know how I can do this. I assume that select is used, similar to the following:

SELECT [Some data processing in "MyDate"]
, COUNT (ID)
FROM MyTable
GROUP BY [Some types of data manipulation in "MyDate"]

Answer 1

dateadd . .

(0) mod 15, 15- :

select -1 * datediff(minute, 0, mydate) % 15
from mytable

, , , SQL Server Magazine 2007 ( Itzik Ben-Gan):

select dateadd(minute, datediff(minute, 0, mydate), 0)
from mytable

, !

select dateadd(minute, -1 * datediff(minute, 0, mydate) % 15, dateadd(minute, datediff(minute, 0, mydate), 0)), count(ID)
from mytable
group by dateadd(minute, -1 * datediff(minute, 0, mydate) % 15, dateadd(minute, datediff(minute, 0, mydate), 0))

Answer 2

, - , , mod 15 - 15- ,

create table quarterHourly (ID int identity(1, 1), created datetime)

insert into quarterHourly values ('2009-07-29 10:00:00.000') -- 10:00
insert into quarterHourly values ('2009-07-29 10:00:00.010') -- 10:00
insert into quarterHourly values ('2009-07-29 10:15:00.000') -- 10:15
insert into quarterHourly values ('2009-07-29 10:15:00.010') -- 10:15
insert into quarterHourly values ('2009-07-29 10:30:00.000') -- 10:30
insert into quarterHourly values ('2009-07-29 10:30:00.010') -- 10:30
insert into quarterHourly values ('2009-07-29 10:45:00.000') -- 10:45
insert into quarterHourly values ('2009-07-29 10:45:00.010') -- 10:45
insert into quarterHourly values ('2009-07-29 11:00:00.000') -- 11:00
insert into quarterHourly values ('2009-07-29 11:00:00.010') -- 11:00
insert into quarterHourly values ('2009-07-29 10:31:00.010') -- 10:30
insert into quarterHourly values ('2009-07-29 10:44:00.010') -- 10:30

select dateadd(mi, datediff(mi, 0, created) - datepart(mi, created) % 15, dateadd(dd, 0, datediff(dd, 0, created))), count(*)
from quarterHourly
group by dateadd(mi, datediff(mi, 0, created) - datepart(mi, created) % 15, dateadd(dd, 0, datediff(dd, 0, created)))

Answer 3

DATEPART(year, MyDate), 
DATEPART(month, MyDate), 
DATEPART(day, MyDate), 
DATEPART(hour, MyDate), 
CASE 
WHEN DATEPART(minute, MyDate) BETWEEN 0 AND 14 THEN 0
WHEN DATEPART(minute, MyDate) BETWEEN 15 AND 29 THEN 1
WHEN DATEPART(minute, MyDate) BETWEEN 30 AND 44 THEN 2
WHEN DATEPART(minute, MyDate) BETWEEN 45 AND 59 THEN 3
END

(, , ).

, , - 15 . , , , .