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?