Re: SQL Sum Daily Data Weekly

<trpost_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. 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 Received on Mon Jan 21 2008 - 08:02:59 CST