SQL Sum Daily Data Weekly
trp...@gmail.com
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
Charles Hooper
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.
cc
<trp...@gmail.com> wrote in message
news:1f52f332-27ce-4a06...@q77g2000hsh.googlegroups.com...
Using the to_char(date_column, 'IW') function will return the week of the
year for the date. Going one step further, to_char(date_column, 'YYYYIW')
will return the week within a year. Finally, to_number(to_char(date_column,
'YYYYIW')) will return the value as a number.
Therefore, the following queries may be worth exploring, at least for
ideas...
select report_date, to_char(report_date, 'YYYYIW') from report_table /*
This will show the correspondence between the report_date and its year and
week */
select to_char(report_date, 'YYYYIW'), sum(total)
from report_table
where to_number(to_char(report_date,'YYYYIW')) >=
to_number(to_char(to_date( '&one_year_ago'),'YYYYIW'))
group by to_char(report_date, 'YYYYIW')
/* Allows totals to be grouped within a week within a year, starting at a
specified date */
Remember that the date "one year ago" can be obtained by report_date-365 or
add_months(report_date),-12). These date calculations may result in
different answers. Just for grins, try it with 1/17/2005 and you'll see the
difference.
hth
fitzj...@cox.net
As Oracle sees it.
> Going one step further, to_char(date_column, 'YYYYIW')
> will return the week within a year. Finally, to_number(to_char(date_column,
> 'YYYYIW')) will return the value as a number.
>
True. But, why go through all of those gyrations?
> Therefore, the following queries may be worth exploring, at least for
> ideas...
>
> select report_date, to_char(report_date, 'YYYYIW') from report_table /*
> This will show the correspondence between the report_date and its year and
> week */
The OP has a week, apparently, defined in reference to a starting
date, which your calculations won't support.
>
> select to_char(report_date, 'YYYYIW'), sum(total)
> from report_table
> where to_number(to_char(report_date,'YYYYIW')) >=
> to_number(to_char(to_date( '&one_year_ago'),'YYYYIW'))
> group by to_char(report_date, 'YYYYIW')
> /* Allows totals to be grouped within a week within a year, starting at a
> specified date */
Again, why go through all of the gyrations for to_number conversion
when it isn't necessary:
select to_char(report_date, 'YYYYIW'), sum(c2), sum(c3), sum(c4)
from t1
where to_char(report_date,'YYYYIW') >= '&one_year_ago'
group by to_char(report_date, 'YYYYIW');
And, yes, it does provide 'different' totals which may not satisfy
the OPs original condition:
"I am looking to pull the last 52 weeks of data based on a start
date"
which, to me, indicates a 'rolling' window where the submitted date is
ending date for the week of interest:
"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)... "
This is something your query example cannot provide. Which is not
saying this is a bad example, but noting that it's not applicable in
this situation.
>
> Remember that the date "one year ago" can be obtained by report_date-365 or
> add_months(report_date),-12). These date calculations may result in
> different answers. Just for grins, try it with 1/17/2005 and you'll see the
> difference.
>
> hth- Hide quoted text -
>
> - Show quoted text -
David Fitzjarrell
cc
The to_char(date_column, 'IW') or to_char(date_column,'WW') will return
the week as Oracle sees it. However, offsetting the report_date by some
constant will usually get the week information reported by the database to
coincide with the week information sought by the user.
The gyrations illustrate that date information can manipulated in a number
of ways. This lays the groundwork for extending date manipulations in the
SQL query
It is true that the previous reply did not reference a starting date. The
more important point seemed to be correlating reporting dates to weeks so
that evaluation of the selection criteria would be based on the week to
which the data belonged.
The original reply was not intended to be a complete solution. A more
complete solution follows ( with gyrations ;-> )
Offsetting the report_date by two days should align reporting weeks with
calendar weeks. Please verify that behavior - it's better than blind trust
in a newsgroup reply. The "with" portion of the query performs the
computations of dates that will be used to filter report_dates from the
reporting table, performing the calculations once, and labeling them
appropriately. The date format was changed from 'IW' to 'WW' so that dates
stay in the same calendar year - it's an ISO issue, check the Oracle
documentation for particulars. When run in SQL*Plus, the &in_date value
allows the user to supply an argument to drive the date calculations. BTW,
this query was written against a 10.2 database - not sure what database
version was used in the original post.
As always, it's for illustrative purposes only, tailor it as needed. If it
is of value, use it - otherwise, send it to the bit bucket.
with computations as (
select to_number(to_char(to_date('&in_date','mm/dd/yyyy')+2,'ww')) as
this_week,
to_number(to_char(to_date('&in_date','mm/dd/yyyy')+2,'yyyy')) as
this_year,
to_number(to_char(to_date('&in_date','mm/dd/yyyy')+2,'ww')) - 52
this_week_minus_52,
to_char(to_date('&in_date','mm/dd/yyyy')+2,'yyyy') ||
to_char(to_date('&in_date','mm/dd/yyyy')+2,'ww') - 52 as same_year,
to_number(to_char(trunc(to_date('&in_date','mm/dd/yyyy')+2,'yyyy')-1,'ww'))
weeks_in_prior_yr
from dual
)
select max(report_date), sum(cc.starts), sum(cc.continues), sum(cc.total)
from cc
where to_number(to_char(report_date+2, 'yyyyww')) >=
(select case when this_week_minus_52 < 1 then
this_year-1 || weeks_in_prior_yr - (52 - this_week)
else
same_year
end as earliest_period
from computations)
group by to_char(report_date+2, 'yyyyww')
<fitzj...@cox.net> wrote in message
news:b23dfa49-9446-463c...@e6g2000prf.googlegroups.com...