Re: Calculating historical MTD's & YTD's

Paul wrote:

> I've been trying to figure out the modt efficient way to dor this for
> the best part of a day. Here's the situation:
>
> Each week we receive around 10 million rows of transaction data. This
> data needs to be immediately summed to include the rolling month to date
> and year to date and then appended to a summary table.
>
> The batch contains one week and one week only and is unique by Week
> Number, UPC, and Store Number.
>
> -----------------------------
> Transactional table
>
> WEEK number(6,0) STORE varchar2(8) UPC varchar2(13) UNITS number(18,0)
> DOLLARS number(18,2)
> -----------------------------
>
> Summary table
>
> WEEK number(6,0) UPC varchar2(13) UNITS number(18,0) DOLLARS
> number(18,2) UNITS_MTD number(18,0) DOLLARS_MTD number(18,2) UNITS_YTD
> number(18,0) DOLLARS_YTD number(18,2)
> -----------------------------
>
> The problem is actually two. First is how to calculate the 'to date'
> values for the appended data as it comes in. But the second is the
> real problem and that is to calculate these 'to date' values for all
> our existing historical transactional data. We have just over 1
> billion rows.
>
> Can someone advise on how best to do this weekly and also the quickest
> way to retrofit these columns to our history?
>
> Thanks Paul
>
> --
> Posted via dBforums
> http://dbforums.com

The problem is that you are dealing with data when you should be dealing with metadata. Stop doing your calculations on the billions of individual rows.

Each time you do a calculation write a single record that sums up that specific week.

Then when the next week's worth of data comes in you only need to compare it with, at most 51 other rows of data.

Daniel A. Morgan Received on Thu May 10 2001 - 23:28:49 CDT