CODEWITHSUNDEEP
sqlsql-servert-sql
Craig
Craig

Reputation: 36816

TSQL Weighted Average

I am trying to create a TSQL function to calculate weighted average cost for inventory calculation. So given the following table structure

ProductId | DatePurchased | Qty | Cost
--------- | ------------- | --- | ----
1         | Jan 1         | 10  | 1.50
1         | Jan 10        | 5   | 2.00
1         | Jan 20        | 7   | 2.50

Now if on Jan 21 someone purchased 15 the weighted cost would be

((7 * 2.5) + (5 * 2.0) + (3 * 1.5)) / 15 = 2.13

Basically, that is the average cost of 7 from Jan 20, 5 from Jan 10 and 3 from Jan 1.

I am sure this can be done with some kind of recursive CTE, but by someone smarter than me.

Upvotes: 3

Views: 604

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269783

A weight average is simple:

select sum(qty * cost) / sum(qty)
from t;

You are looking for something else. Perhaps an "allocated" average:

select sum(case when cume_qty - qty < 15 then qty * cost
                else (cume_qty - 15) * cost
           end) as allocated_average
from (select t.*,
             sum(qty) over (partition by productid order by date desc) as cume_qty
      from t
     ) t
where cume_qty - qty < 15 and
      cume_qty >= 15;

Upvotes: 3

Related Questions