Try this, it will be easiest if you have an interval table with the columns for the current interval and next interval. You can then use a between in your select statement.
create table period (
TimeInt time not null,
NextTimeInt time not null,
primary key (TimeInt,NextTimeInt)
);
insert into period values('0:00:00','0:30:00');
insert into period values('0:30:00','1:00:00');
insert into period values('1:00:00','1:30:00');
insert into period values('1:30:00','2:00:00');
insert into period values('2:00:00','2:30:00');
insert into period values('2:30:00','3:00:00');
insert into period values('3:00:00','3:30:00');
insert into period values('3:30:00','4:00:00');
insert into period values('4:00:00','4:30:00');
insert into period values('4:30:00','5:00:00');
insert into period values('5:00:00','5:30:00');
insert into period values('5:30:00','6:00:00');
insert into period values('6:00:00','6:30:00');
insert into period values('6:30:00','7:00:00');
insert into period values('7:00:00','7:30:00');
insert into period values('7:30:00','8:00:00');
insert into period values('8:00:00','8:30:00');
insert into period values('8:30:00','9:00:00');
insert into period values('9:00:00','9:30:00');
insert into period values('9:30:00','10:00:00');
insert into period values('10:00:00','10:30:00');
insert into period values('10:30:00','11:00:00');
insert into period values('11:00:00','11:30:00');
insert into period values('11:30:00','12:00:00');
insert into period values('12:00:00','12:30:00');
insert into period values('12:30:00','13:00:00');
insert into period values('13:00:00','13:30:00');
insert into period values('13:30:00','14:00:00');
insert into period values('14:00:00','14:30:00');
insert into period values('14:30:00','15:00:00');
insert into period values('15:00:00','15:30:00');
insert into period values('15:30:00','16:00:00');
insert into period values('16:00:00','16:30:00');
insert into period values('16:30:00','17:00:00');
insert into period values('17:00:00','17:30:00');
insert into period values('17:30:00','18:00:00');
insert into period values('18:00:00','18:30:00');
insert into period values('18:30:00','19:00:00');
insert into period values('19:00:00','19:30:00');
insert into period values('19:30:00','20:00:00');
insert into period values('20:00:00','20:30:00');
insert into period values('20:30:00','21:00:00');
insert into period values('21:00:00','21:30:00');
insert into period values('21:30:00','22:00:00');
insert into period values('22:00:00','22:30:00');
insert into period values('22:30:00','23:00:00');
insert into period values('23:00:00','23:30:00');
insert into period values('23:30:00','0:00:00');
create table testdate (
purKey int not null auto_increment primary key,
qty int not null default 1,
dollars int not null default 0,
orderts datetime not null default '0000-00-00-00-00-00'
);
insert into testdate values(null,1,5,'2003-07-06-12-01-03');
insert into testdate values(null,1,8,'2003-07-06-12-04-03');
insert into testdate values(null,1,12,'2003-07-06-12-41-03');
insert into testdate values(null,1,4,'2003-07-06-13-01-03');
insert into testdate values(null,1,7,'2003-07-06-13-15-03');
insert into testdate values(null,1,11,'2003-07-06-13-41-03');
select
TimeInt,
count(*) as "PurchasesMade",
sum(if( dollars between 0 and 5,1,0)) as "cat1",
sum(if( dollars between 6 and 10,1,0)) as "cat2",
sum(if( dollars between 11 and 15,1,0)) as "cat3"
from period P, testdate D
where extract(HOUR_SECOND from (interval 30 minute + D.orderts)) between P.TimeInt and P.NextTimeInt
group by P.TimeInt;
+----------+---------------+------+------+------+
| TimeInt | PurchasesMade | cat1 | cat2 | cat3 |
+----------+---------------+------+------+------+
| 12:30:00 | 2 | 1 | 1 | 0 |
| 13:00:00 | 1 | 0 | 0 | 1 |
| 13:30:00 | 2 | 1 | 1 | 0 |
| 14:00:00 | 1 | 0 | 0 | 1 |
+----------+---------------+------+------+------+
4 rows in set (0.01 sec)
Cheers,
abombss