Solved

# SQL query aggregate data for week - db2 ver7

Posted on 2006-04-10
792 Views
I wanted to run a query to calculate peak per week from a table that has date time value column and the table has data for a day, I wanted to aggregate on that data for a week and find the peak value.

date time field is looking like this For Apr1 data  2006-04-01-00.00.00 starting to 2006-04-30-23:59:00 to end.

I wanted to aggregate the value for a week like starting Apr1-7,8-14,15-22,23-30 on a number column (amount) and then find the peak value (I suppose we can use max ) and get the value in 1 SQL query.

Any ideas
0
Question by:mahjag

LVL 5

Accepted Solution

if you table is something like this:

create table agg(
dt date,
amount integer
);

you can use this:

select
case
when day(dt) >= 1 and day(dt) <= 7 then 'APR_1-7'
when day(dt) >= 8 and day(dt) <= 14 then 'APR_8-14'
when day(dt) >= 15 and day(dt) <= 21 then 'APR_15-21'
when day(dt) >= 22 and day(dt) <= 28 then 'APR_22-28'
when day(dt) >= 29 and day(dt) <=30 then 'APR_29-30'
end as weekEnding,
max(amount) as amount
from agg
group by
case
when day(dt) >= 1 and day(dt) <= 7 then 'APR_1-7'
when day(dt) >= 8 and day(dt) <= 14 then 'APR_8-14'
when day(dt) >= 15 and day(dt) <= 21 then 'APR_15-21'
when day(dt) >= 22 and day(dt) <= 28 then 'APR_22-28'
when day(dt) >= 29 and day(dt) <=30 then 'APR_29-30'
end;

i would assume that maybe you'd use a procedure to make this more universal for different months...
0

LVL 2

Assisted Solution

This should be general:

SELECT monday, weekamount
FROM (SELECT SUM(amount) AS weekamount, monday
FROM (SELECT dt - dayofweek_iso(dt) days + 1 day AS monday,
amount
FROM agg) AS X
GROUP BY monday) AS Y
ORDER BY weekamount DESC FETCH FIRST ROW ONLY;

The usage of ORDER BY and FFRO allows you to retrieve the beginning date of the week without having to do a rejoin.
I chose to normalize to a specific date instead of grouping by year and week directly because the last week of the year would be hard to group properly.

Of course the inner subquery is not strictly needed. I simply didn't want to repeat the expression in the group by clause.

Cheers
Serge
0

## Featured Post

### Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…