Re: Aggregate by calandar weeks?
dsw@cbnewsm.cb.att.com (dale.s.walkowicz) writes:
>I need to write a query to aggregate numbers by calandar weeks
>(Mondy to Sunday). It isn't obvous how to do that. Has anyone figured
>this out before (or figured out it can't be done)? I'd appreciate
>any help.
You can use the WEEKDAY() function to do what you want, but the fact
that you want Monday-Sunday rather than Sunday-Saturday complicates it
somewhat. WEEKDAY(date) returns 0 for Sunday, 1 or Monday, ... 6 for
Saturday. Here's a two-step solution I came up with to meet your
specific request:
{* get the Monday-Saturday aggregates and store them in Monday's date *}
SELECT (datefield - WEEKDAY(datefield) + 1) wdate, SUM(aggfld) aggsum
FROM table
WHERE WEEKDAY(datefield) > 0
GROUP BY 1
UNION
{* get Sunday's aggregates and store them in last Monday's date *}
SELECT (datefield - 6) wdate, SUM(aggfld) aggsum
FROM table
WHERE WEEKDAY(datefield) = 0
GROUP BY 1
{* put all results into a temp table to fetch out on next step *}
INTO TEMP temptable;
{* Combine the (possible) 2 rows with the same date into 1 aggregate *}
SELECT wdate, SUM(aggsum)
FROM temptable
GROUP BY 1
ORDER BY 1;
I used SUM, but any aggregate should work the same. If you can live
with Sunday-Saturday results, the query is much simplified:
{* get the Sunday-Saturday aggregates and store them in Sunday's date *}
SELECT (datefield - WEEKDAY(datefield)), SUM(aggfld)
FROM table
GROUP BY 1
ORDER BY 1;
================
Dennis J. Pimple
Informix CSE / Denver