Stock Level and Stock Value As of Date

[kbryndum]

Hi,

I need to calculate stock level and it's value as of dates.

The stock level is calcalated as a sum of all quantities less than or equal as of a date.

I have used a solution from Mr. Witherspoon, where you generate a AsOfDate table, which is linked to the stockchange table on the date field:

AsOfDate, Date
01/01, 01/01
02/01, 02/01
02/01, 01/01
03/01, 03/01
03/01, 02/01
03/01, 01/01
04/01, 04/01
04/01, 03/01
04/01, 02/01
04/01, 01/01
05/01, 05/01
05/01, 04/01
05/01, 03/01
05/01, 02/01
05/01, 01/01

This works great!

Now I need to calculate the value of the stock as of a date. The issue is that the cost price of the items to be used is based on the cost price as of the date for ALL items in stock.

So I basically I have a table with cost prices pr. item pr. activation dates (or could have been From and To Dates), which I need to use.
It would be wrong (in my case) to join the cost price to the stock change table, as we want to change the value of the stock, when cost prices is changed.

So if there is 3 pieces in stock at 01/01 and the cost price of an item is 1000 at 01/01, then the value of the stock at 01/01 is 3000 (3*1000).
If there is 11 pieces in stock at 06/01 and the cost price has change to 2000 at 06/01, then the value of the stock at 06/01 is 22000 (11*2000).

My idea is to modify the AsOf table to include ItemNo. and its cost price for each AsOf date, and then multiply the ItemPrice is this table to the quantity:

Item      AsOfDate      Date      ItemPrice
A      01/01/2010      01/01/2010      1000
A      02/01/2010      01/01/2010      1000
A      02/01/2010      02/01/2010      1000
A      03/01/2010      01/01/2010      1000
A      03/01/2010      02/01/2010      1000
A      03/01/2010      03/01/2010      1000
A      04/01/2010      01/01/2010      1000
A      04/01/2010      02/01/2010      1000
A      04/01/2010      03/01/2010      1000
A      04/01/2010      04/01/2010      1000
A      05/01/2010      01/01/2010      1500
A      05/01/2010      02/01/2010      1500
A      05/01/2010      03/01/2010      1500
A      05/01/2010      04/01/2010      1500
A      05/01/2010      05/01/2010      1500
A      06/01/2010      01/01/2010      2000
A      06/01/2010      02/01/2010      2000
A      06/01/2010      03/01/2010      2000
A      06/01/2010      04/01/2010      2000
A      06/01/2010      05/01/2010      2000
A      06/01/2010      06/01/2010      2000

I have made a test and works fine (se attached), but my problem is that my amount of data is around 40 mill. records, and that the modified AsOf table is going to be very big, as there is more dimensions to it, than just ItemNo, which is around 20.000 different pieces. Other dimensions is legal entity and pricelist version.

As I see it I need to generate the above table, but for each key (ItemNo., Legal unit and version).

My question is:

Is this the way to do it, or is there another approach? Any help is much appreciated.

Thanks!

/Kim