Time in SQL QUERY

[logic4fun]

Hi all,
I have a DB2 timestamp YYYY-MM-DD-HH.MM.SS and i need to pull data by Half Hour Intervals.
here is the following table
purchasesMade US$ timeStamp
1 5 2003-07-06-12-01-03
1 8 2003-07-06-12-04-03
1 12 2003-07-06-12-41-03
1 4 2003-07-06-13-01-03
1 7 2003-07-06-13-15-03
1 11 2003-07-06-13-41-03

For the above data i need result as
TimeIntvl purchasesMade cat-1 cat-2 cat-3
12:30 2 1 1 0
13:00 1 0 0 1
13:30 2 1 1 0
14:00 1 0 0 1


The three categories are between 0-5$ , 6-10$ and 11-15$.
I know i can group by hour in DB2 and also by minute..but confused in getting half hour for the above case.

ANY Suggestions..
thanks in advance
logic4fun

 

[abombss]

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