Re: SQL Sum Daily Data Weekly
Date: Fri, 18 Jan 2008 19:29:55 -0800 (PST)
Message-ID: <05d048e1-255e-4500-8da6-57fe3e17b332@l1g2000hsa.googlegroups.com>
On Jan 18, 4:03 pm, trp..._at_gmail.com wrote:
> I am looking to pull weekly stats from daily data, but am not sure the
> best way to accomplish this. I would provide a start date, and then I
> am looking to pull the last 52 weeks of data based on a start date. So
> if I choose 1/18/2008 then I would get summed daily data for 1/12/2008
> - 1/18/2008, 1/5/2008-1/11/2008 ......etc(going back 52 weeks)...
>
> The additional challenge is that my data is daily, but I want to sum
> all columns except REPORT_DATE to get weekly totals:
>
> example table data:
>
> REPORT_DATE STARTS CONTINUES TOTAL
> 12/31/2007 50 100 150
> 1/01/2008 30 100 180
> 1/02/2008 60 100 160
> 1/03/2008 40 100 140
> 1/04/2008 20 100 130
> 1/05/2008 10 100 110
> 1/06/2008 70 100 170
> 1/07/2008 90 100 190
> 1/08/2008 60 100 160
> 1/09/2008 55 100 155
> 1/10/2008 35 100 135
> 1/11/2008 65 100 165
> 1/12/2008 45 100 145
> 1/13/2008 25 100 125
> 1/14/2008 15 100 115
> 1/15/2008 75 100 175
> 1/16/2008 95 100 195
> 1/17/2008 65 100 165
> 1/18/2008 5 100 105
>
> Sample expected results:
> WEEK_ENDING STARTS CONTINUES TOTAL
> 1/18/2008 325 700 1025
> 1/11/2008 385 700 1085
> ...
>
> Thanks
In the future, please post the SQL statements to create the tables and to insert your sample data.
Here is one method, using a table that I constructed with random data:
CREATE TABLE T1(
REPORT_DATE DATE,
C2 NUMBER(10), C3 NUMBER(10), C4 NUMBER(10),
PRIMARY KEY(REPORT_DATE)); Now, to insert 450 days worth of random data: INSERT INTO
T1
SELECT
REPORT_DATE,
C2,
C3,
C2+C3
FROM
(SELECT
TRUNC(SYSDATE-450)+ROWNUM REPORT_DATE, DBMS_RANDOM.VALUE(5,100) C2,
100 C3
FROM
DUAL
CONNECT BY
LEVEL<=450);
COMMIT;
Let's take a look at the first couple of rows in the table in
descending order of the REPORT_DATE:
SELECT
*
FROM
T1
WHERE
REPORT_DATE>=TRUNC(SYSDATE-19)
ORDER BY
REPORT_DATE DESC;
REPORT_DA C2 C3 C4
--------- ---------- ---------- ---------- 18-JAN-08 39 100 139 17-JAN-08 56 100 156 16-JAN-08 69 100 169 15-JAN-08 90 100 190 14-JAN-08 65 100 165 13-JAN-08 56 100 156 12-JAN-08 83 100 183 11-JAN-08 36 100 136 10-JAN-08 88 100 188 09-JAN-08 81 100 181 08-JAN-08 68 100 168 07-JAN-08 48 100 148 06-JAN-08 29 100 129 05-JAN-08 88 100 188 04-JAN-08 88 100 188 03-JAN-08 41 100 141 02-JAN-08 51 100 151 01-JAN-08 12 100 112 31-DEC-07 7 100 107 30-DEC-07 38 100 138...
Now, let's try an experiment to see if we can identify the date ranges
of our weeks, given a particular date as the end date. We can step
through each of the weeks by using the CONNECT BY syntax, and the
dates are simple calculations given that there are seven days in a
week:
SELECT
TO_DATE('01/18/2008','MM/DD/YYYY')-(ROWNUM-1)*7-6 START_DATE,
TO_DATE('01/18/2008','MM/DD/YYYY')-(ROWNUM-1)*7 END_DATE
FROM
DUAL
CONNECT BY
LEVEL<=52
START_DAT END_DATE
--------- --------- 12-JAN-08 18-JAN-08 05-JAN-08 11-JAN-08 29-DEC-07 04-JAN-08 22-DEC-07 28-DEC-07 15-DEC-07 21-DEC-07 08-DEC-07 14-DEC-07 01-DEC-07 07-DEC-07 24-NOV-07 30-NOV-07 17-NOV-07 23-NOV-07
...
If we slide the above into an inline view and join it to the T1 table,
such that the T1.REPORT_DATE falls between the START_DATE and
END_DATE, we can then group on the END_DATE. One problem that you
might have with the BETWEEN syntax is that weeks could be skipped if
there is no data for the week. To work around this, we can use an
outer join between the view and the table, making certain that if the
REPORT_DATE exists in the table, the data for that row will be grouped
in the appropriate week.
SELECT
W.END_DATE,
SUM(T1.C2) STARTS, SUM(T1.C3) CONTINUES, SUM(T1.C4) TOTAL
FROM
(SELECT
TO_DATE('01/18/2008','MM/DD/YYYY')-(ROWNUM-1)*7-6 START_DATE, TO_DATE('01/18/2008','MM/DD/YYYY')-(ROWNUM-1)*7 END_DATE FROM
DUAL
CONNECT BY
LEVEL<=52) W,
T1
WHERE
W.START_DATE<=T1.REPORT_DATE(+)
AND W.END_DATE>=T1.REPORT_DATE(+)
GROUP BY
W.END_DATE
ORDER BY
W.END_DATE DESC; END_DATE STARTS CONTINUES TOTAL
--------- ---------- ---------- ---------- 18-JAN-08 458 700 1158 11-JAN-08 438 700 1138 04-JAN-08 305 700 1005 28-DEC-07 352 700 1052 21-DEC-07 264 700 964 14-DEC-07 362 700 1062 07-DEC-07 394 700 1094 30-NOV-07 327 700 1027...
Let's try again, this time specifying an end date that will have no
data:
SELECT
W.END_DATE,
SUM(T1.C2) STARTS, SUM(T1.C3) CONTINUES, SUM(T1.C4) TOTAL
FROM
(SELECT
TO_DATE('01/30/2008','MM/DD/YYYY')-(ROWNUM-1)*7-6 START_DATE, TO_DATE('01/30/2008','MM/DD/YYYY')-(ROWNUM-1)*7 END_DATE FROM
DUAL
CONNECT BY
LEVEL<=52) W,
T1
WHERE
W.START_DATE<=T1.REPORT_DATE(+)
AND W.END_DATE>=T1.REPORT_DATE(+)
GROUP BY
W.END_DATE
ORDER BY
W.END_DATE DESC; END_DATE STARTS CONTINUES TOTAL
--------- ---------- ---------- ---------- 30-JAN-08 23-JAN-08 95 200 295 16-JAN-08 487 700 1187 09-JAN-08 443 700 1143 02-JAN-08 273 700 973 26-DEC-07 344 700 1044 19-DEC-07 326 700 1026 12-DEC-07 351 700 1051...
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Jan 18 2008 - 21:29:55 CST