# 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\$.

IDQUANTITYTOTAL_PRICEUNIT_COST (calculated)
11010010.0

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

IDQUANTITYTOTAL_PRICEUNIT_COST (calculated)
11010010.0
21020015.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

IDQUANTITYTOTAL_PRICEUNIT_COST (calculated)
11010010.0
21020015.0
3-10-30015.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\$.

IDQUANTITYTOTAL_PRICEUNIT_COST (calculated)
11010010.0
21020015.0
3-10-30015.0
41010012.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.```