# 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
```

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.