SQL query aggregate data for week - db2 ver7

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
mahjagAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ocgstylesCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srielauCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.