Calculating historical MTD's & YTD's

From: Paul <paul_at_dbforums.com>
Date: Fri, 11 May 2001 02:26:32 GMT
Message-ID: <s5IK6.2853$Zf1.9121@news1.onlynews.com>

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

Received on Thu May 10 2001 - 21:26:32 CDT