SQL query aggregate data for week - db2 ver7

Posted on 2006-04-10
Medium Priority
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

Accepted Solution

ocgstyles earned 100 total points
ID: 16423271
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...

Assisted Solution

srielau earned 100 total points
ID: 16441019
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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month16 days, 13 hours left to enroll

862 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