CODEWITHSUNDEEP
sqlsql-servert-sql
Bharat
Bharat

Reputation: 3521

Calculate Weighted Cost (Avg) of a product in T-SQL

I have a Product table with it's Sale, Purchase, Begin and End stock for a given day. I have omitted date and other products in the below sample for simplicity. I am trying to get the Avg Cost of a product using T-SQL. I am puzzled with this problem since it relies on the previous record(day)'s AvgCost which is null to start with.

Sample Data

DECLARE @tbl TABLE (Id INT IDENTITY, ProductId INT, 
    BeginInvt DECIMAL(9,2), SaleQty DECIMAL(9,2), SalePrice DECIMAL(5,3), 
    PurQty DECIMAL(9,2) NULL, PurCost DECIMAL(5,3) NULL, EndingInvt DECIMAL(9,2))

INSERT @tbl (ProductId, BeginInvt, SaleQty, SalePrice, PurQty, PurCost, EndingInvt )
VALUES 
(1,3000,500,2.000,NULL,NULL,2500),
(1,2500,250,2.000,2000,1.800,4250),
(1,4250,300,2.000,NULL,NULL,3950),
(1,3950,900,2.000,4000,1.850,7050),
(1,7050,300,2.000,NULL,NULL,6750),
(1,6750,300,2.000,NULL,NULL,6450),
(1,6450,300,2.000,5000,1.750,11150),
(1,11150,300,2.000,NULL,NULL,10850)

I want to get the AvgCost for the above rows. Given that initial stock of 3000 items' avg cost is 1.9. The solution should have the result like this

I am looking for output with the column AvgCost as below. This table can have different products. Once I have the solution I can apply window function to partition the calculations.

Expected Result

Id   ProductId   BeginInvt   SaleQty  SalePrice  PurQty    PurCost  EndingInvt  AvgCost
---- ----------- ----------- -------- ---------- --------- -------- ----------- --------
1    1           3000.00     500.00   2.000      NULL      NULL     2500.00     1.90000
2    1           2500.00     250.00   2.000      2000.00   1.800    4250.00     1.85556
3    1           4250.00     300.00   2.000      NULL      NULL     3950.00     1.85556
4    1           3950.00     900.00   2.000      4000.00   1.850    7050.00     1.85276
5    1           7050.00     300.00   2.000      NULL      NULL     6750.00     1.85276
6    1           6750.00     300.00   2.000      NULL      NULL     6450.00     1.85276
7    1           6450.00     300.00   2.000      5000.00   1.750    11150.00    1.80789
8    1           11150.00    300.00   2.000      NULL      NULL     10850.00    1.80789

AvgCost for a row can be calculated by doing the weighted avg of BeginInvt and Pur values as follows:

((BeginInvt*prevous_day_avgcost)+(PurQty*PurCost))/(BeginInvt+PurQty)

prevous_day_avgcost is the problem for me with set based calculations

Thanks for the help...

Upvotes: 0

Views: 1043

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

One way to do it using a recursive cte. This is because you wouldn't know the avgCost for each row until the previous calculation is complete. So you can do this iteratively.

with rownums as (select t.*,row_number() over(partition by productId order by id) as rnum from @tbl t)
,cte as (select rnum,id,ProductId, BeginInvt, SaleQty, SalePrice, 
         PurQty, PurCost, EndingInvt ,cast(1.9 as decimal(38,5)) as avgCost --replace this with a calculation if you know it.
         from rownums 
         where rnum=1
         union all
         select r2.rnum,r2.id,r2.ProductId, r2.BeginInvt, r2.SaleQty, r2.SalePrice, r2.PurQty, r2.PurCost, r2.EndingInvt, 
         cast(((r2.BeginInvt*r1.avgCost)+(coalesce(r2.PurQty,0)*coalesce(r2.PurCost,0)))/(r2.BeginInvt+coalesce(r2.PurQty,0)) as decimal(38,5))
         from cte r1
         join rownums r2 on r1.productId=r2.productId and r2.rnum=r1.rnum+1
        ) 
select ProductId, BeginInvt, SaleQty, SalePrice, PurQty, PurCost, EndingInvt, avgCost
from cte

Sample Demo

Upvotes: 1

Related Questions