Aggregates for date ranges

I have a table called 'tab1' containing the fields trans_date (date of transacton), pid (product ID), amt (amount). I want to retrieve the total amount for every 5 days starting from a given date until an end date. For example, start date ='2002-10-02', and end date = '2002-10-19'. I want to retrieve the data as:

Period                     Amount
2002-10-02 to 2002-10-06    30000
2002-10-07 to 2002-10-11    24000
2002-10-12 to 2002-10-16    30500
2002-10-17 to 2002-10-19    12000

How do I write the SQL to get this result in DB2?

This would be really easy if you could join your table with a special table consisting of the start and end dates. For example, suppose we had such a table, called datestable:

periodstart periodend
2002-10-02  2002-10-06
2002-10-07  2002-10-11
2002-10-12  2002-10-16
2002-10-17  2002-10-21

Then the desired results can be obtained by

select char(periodstart)
    || ' to '
    || char(periodend)   as Period
     , sum(amt)          as Amount
  from tab1
inner
  join datestable
    on trans_date between periodstart 
                      and periodend
 where trans_date between startdate
                      and enddate
group
    by periodstart 
     , periodend

So when you run this query, every selected transaction is joined to the datestable based on the period it falls into as determined in the ON clause, while the entire range of transactions that is selected is based on the date range specified in the WHERE clause.

Now, if only we had such a datestable handy. We could create one, but it would be such a nuisance to maintain.

A better solution is to generate the period start and end dates on the fly, as it were. For this, you will need a permanent table, but it is one that has a thousand uses and you will need to create it only once, and never maintain it (unless you started out with too few and need to add more numbers). It is called an integer table.

create table integers ( i integer );
insert into integers(i) values(0);
insert into integers(i) values(1);
insert into integers(i) values(2);
insert into integers(i) values(3);
insert into integers(i) values(4);
insert into integers(i) values(5);
insert into integers(i) values(6);
etc.

Now, use the startdate and enddate parameters to produce the period start and end dates.

select startdate + 5 * i days      as periodstart
     , startdate + 5 * i + 4 days  as periodend
  from integers
 where startdate + 5 * i days <= enddate

So now all that's required is to incorporate this into the original query in place of the datestable.

select char(startdate + 5 * i days)
    || ' to '
    || char(startdate + 5 * i + 4 days)   as Period
     , sum(amt)          as Amount
  from tab1
inner
  join integers
    on trans_date between startdate + 5 * i days 
                      and startdate + 5 * i + 4 days
 where trans_date between startdate
                      and enddate

   and startdate + 5 * i days <= enddate
group
    by i

Neat, eh?