Issue

This Content is from Stack Overflow. Question asked by user19720338

Suppose we have an order table with three columns: an ID (ascending), the quantity and the total price. I want to calculate the moving average price (MAP)..

Looking at the first line where we purchased 10 pieces at a total price of 100$.

ID	QUANTITY	TOTAL_PRICE	UNIT_COST (calculated)
1	10	100	10.0

Then we add a purchase of 10 pieces at a higher total price of 200$.

ID	QUANTITY	TOTAL_PRICE	UNIT_COST (calculated)
1	10	100	10.0
2	10	200	15.0

select SUM(total_price) / SUM(quantity) AS unit_cost from tblItems;

The expected output is 15 (10 pieces at 10$ plus 10 pieces at 20$ makes a total of 20 pieces at an average price of 15$). If we sell 10 pieces, we’ll get

ID	QUANTITY	TOTAL_PRICE	UNIT_COST (calculated)
1	10	100	10.0
2	10	200	15.0
3	-10	-300	15.0

The UNIT_COST cost at the time of sale was 15$. The stock is now down to 10 pieces again.

When buying the item again, 10 pieces at a total price of 100$, we’ll get a stock of 10 pieces at 15$ and 10 pieces at 10$, in total 20 pieces at 12.5$.

ID	QUANTITY	TOTAL_PRICE	UNIT_COST (calculated)
1	10	100	10.0
2	10	200	15.0
3	-10	-300	15.0
4	10	100	12.5

After the new purchase the expected cost will be 12.5.

The question is, how can I inquire about the last cost of this item?

Solution

The query is a recursive query. It calculates the UNIT_COST line by line and use the result for subsequent rows.

with StockCost as
(
      -- anchor member
      select ID, QUANTITY, TOTAL_PRICE, 
             BAL_QTY = QUANTITY,
             UNIT_COST = convert(decimal(10,2), TOTAL_PRICE / QUANTITY)
      from   Stock
      where  ID = 1

      union all

      -- recursive member
      select s.ID, s.QUANTITY, s.TOTAL_PRICE,
             BAL_QTY = c.BAL_QTY + s.QUANTITY,
             UNIT_COST = convert(decimal(10,2), 
                                 case when u.UNIT_COST IS NULL
                                      or   u.UNIT_COST = 0 
                                      then c.UNIT_COST 
                                      else u.UNIT_COST 
                                      end)
      from   Stock s
             inner join StockCost c on S.ID = c.ID + 1
             cross apply
             (
                 select UNIT_COST = ((c.UNIT_COST * BAL_QTY) + s.TOTAL_PRICE) 
                                  / NULLIF((c.BAL_QTY + S.QUANTITY), 0)
             ) u
)
select *
from   StockCost

db<>fiddle demo

This Question was asked in StackOverflow by user19720338 and Answered by Squirrel It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.