Re: SQL Sum Daily Data Weekly

From: cc <chris.colclough_at_nospam.jhuapl.edu>
Date: Thu, 24 Jan 2008 07:59:37 -0500
Message-ID: <fna23r$4cv$1@aplnetnews.jhuapl.edu>

Comments on comments...

  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')

<fitzjarrell_at_cox.net> wrote in message
news:b23dfa49-9446-463c-8fdc-c796b112f351_at_e6g2000prf.googlegroups.com... 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 Thu Jan 24 2008 - 06:59:37 CST