Comparing consecutive weeks data

[foamcows]

Hi, I have a giant list of data that has been collected weekly for the past 6 months. The data is a list of SKU's and the weekly plan for production. I work in production and I am having an issue with the planners constantly changing the forecast on us. I have been gathering this data to help show the number of changes they have in the schedule every week.

The list of SKU's stays the same, as well as the headers for the forecasted weeks of production. The only variable that changes in this week is the quantity.



So to break it down I have a very long table (1.4million rows) with the following.

Capture date: date I took the reading
SKU: PArt Number
Week of production: What week this item will be produced
Qty: The quantity of that SKU to be produced during the week of production.

I am trying to find a way to compare the each weeks data to the one before.

So if I have data from weeks 1-40 for the year, I would like to compare week 1 to 2, 2 to 3, 3 to 4.... etc. I just want to gather the absolute value of the difference in quantity from one week to the next.

It is almost like I want to do some type of For each capture date: abs(Qty of this week-qty of prior week). The output can just be another column called weeklychange.


I do understand that variance will show me the number of changes in the quantity for each week. I have already been able to run this query. What I am hoping to do with the above information is show how far out, on average, the changes to the schedule are each week. In other words, how far out is my planner really thinking? My thoughts are they only really adjust the schedule 1-4 weeks out, which kills my ability to long term plan for production. The problem is I need to show this, thus the need to compare the data.

If anyone could help, it would be appreciated.

[ajetrumpet]

it's called a running total query, and this is how you do it in a simple manner:

http://support.microsoft.com/kb/290136

however, with 1.5 million rows, you might be waiting a while. I would guess maybe 5 minutes at the most? Using a function call too, would take much more time.