SQL query aggregate data for week - db2 ver7

Posted on 2006-04-10
Last Modified: 2012-05-05
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
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:

              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
          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'

    i would assume that maybe you'd use a procedure to make this more universal for different months...
    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,
                                FROM agg) AS X
                 GROUP BY monday) AS Y

    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.


    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    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 ( First, Just open a new email message.  In the To field, type your recipient's fax number 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…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now