Using LAG / LEAD to calculate Inventory Cost and Running Average ?
162208Mar 16 2007 — edited May 13 2008I want to calculate the average cost of sales, so that I should know, what is the cost of Inventory in Hand.
The Negative Quantity shows the Sales quantity, how can I calculate the Average Cost using LAG /LEAD or WINDOWING Function.
The data is :
Qty Rate Amount
10 100 1000
-5
10 100 1000
20 110 2200
-10
The final data should like this, just to give you an Idea.
Qty Rate Amount Cumulative Qty Avg.Rate Amount
10 100 1000 10 10 1000
-5 100 -500 5 100 500
10 100 1000 15 100 1500
20 110 2200 35 105.72 3700
-10 105.72 1050 25 105.72 2650
Best Regards,
Luqman