Re: SQL Sum Daily Data Weekly

Comments embedded.
On Jan 21, 8:02 am, "cc" <chris.colclo..._at_nospam.jhuapl.edu> wrote:
> <trp..._at_gmail.com> wrote in message
>
> news:1f52f332-27ce-4a06-8e06-2abdaf9897ee_at_q77g2000hsh.googlegroups.com...
>
>
>
>
>
> >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
>
> Using the to_char(date_column, 'IW') function will return the week of the
> year for the date.  

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 Received on Tue Jan 22 2008 - 10:06:34 CST