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

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

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

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

After the new purchase the expected cost will be 12.5.

To do this in code c#

public class Model
{
  public decimal QUANTITY { get; set; }
  public decimal TOTAL_PRICE { get; set; }
  public decimal UNIT_COST { get; set; }
}
List<Model> _List = new List<Model>();
Model model = new Model();
model.QUANTITY = 10;
model.TOTAL_PRICE = 100;
_List.Add(model);
model = new Model();
model.QUANTITY = 10;
model.TOTAL_PRICE = 200;
_List.Add(model);
model = new Model();
model.QUANTITY = -10;
model.TOTAL_PRICE = -300;
_List.Add(model);
model = new Model();
model.QUANTITY = 10;
model.TOTAL_PRICE = 100;
_List.Add(model);

Model _model = new Model();
foreach (var item in _List)
{
    if (item != null)
    {
        if (item.QUANTITY < 0)
        {
            _model.QUANTITY = _model.QUANTITY   item.QUANTITY;
            _model.TOTAL_PRICE = (item.QUANTITY * _model.UNIT_COST)   _model.TOTAL_PRICE;
            _model.UNIT_COST = _model.TOTAL_PRICE / _model.QUANTITY;
        }
        else
        {
            _model.QUANTITY = _model.QUANTITY   item.QUANTITY;
            _model.TOTAL_PRICE = _model.TOTAL_PRICE   item.TOTAL_PRICE;
            _model.UNIT_COST = _model.TOTAL_PRICE / _model.QUANTITY;
        }
    }
}
Console.WriteLine(_model.UNIT_COST);
Console.ReadLine();

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

CodePudding user response：

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