Inventory Average Cost Calculation in SQL
You can use the MODEL clause to do this recursive calculation
Create sample table and insert data
create table costs (order_id int, volume int, price numeric(16,4), type char(1));
insert into costs (order_id, volume, price) values (1,1000,100);
insert into costs (order_id, volume, price) values (2,-500,110);
insert into costs (order_id, volume, price) values (3,1500,80);
insert into costs (order_id, volume, price) values (4,-100,150);
insert into costs (order_id, volume, price) values (5,-600,110);
insert into costs (order_id, volume, price) values (6,700,105);
The query (EDITED changing rules iterate(1000) to rules automatic order implements the MODEL clause as it is intended to function, i.e. top to bottom sequentially. It also took the query from 0.44s to 0.01s!)
select order_id, volume, price, total_vol, total_costs, unit_costs
from (select order_id, volume, price,
volume total_vol,
0.0 total_costs,
0.0 unit_costs,
row_number() over (order by order_id) rn
from costs order by order_id)
model
dimension by (order_id)
measures (volume, price, total_vol, total_costs, unit_costs)
rules automatic order -- iterate(1000)
( total_vol[any] = volume[cv()] + nvl(total_vol[cv()-1],0.0),
total_costs[any] =
case SIGN(volume[cv()])
when -1 then total_vol[cv()] * nvl(unit_costs[cv()-1],0.0)
else volume[cv()] * price[cv()] + nvl(total_costs[cv()-1],0.0)
end,
unit_costs[any] = total_costs[cv()] / total_vol[cv()]
)
order by order_id
Output
ORDER_ID VOLUME PRICE TOTAL_VOL TOTAL_COSTS UNIT_COSTS
1 1000 100 1000 100000 100
2 -500 110 500 50000 100
3 1500 80 2000 170000 85
4 -100 150 1900 161500 85
5 -600 110 1300 110500 85
6 700 105 2000 184000 92
This site has a good tutorial on the MODEL clause
- http://www.sqlsnippets.com/en/topic-11663.html
The EXCEL sheet for the data above would look like this, with the formula extended downwards
A B C D E F
---------------------------------------------------------------------------
1| order_id volume price total_vol total_costs unit_costs
2| 0 0 0
3| 1 1000 100 =C4+E3 =IF(C4<0,G3*E4,F3+C4*D4) =F4/E4
4| 2 -500 110 =C5+E4 =IF(C5<0,G4*E5,F4+C5*D5) =F5/E5
5| 3 1500 80 =C6+E5 =IF(C6<0,G5*E6,F5+C6*D6) =F6/E6
6| 4 -100 150 =C7+E6 =IF(C7<0,G6*E7,F6+C7*D7) =F7/E7
7| 5 -600 110 =C8+E7 =IF(C8<0,G7*E8,F7+C8*D8) =F8/E8
8| 6 700 105 =C9+E8 =IF(C9<0,G8*E9,F8+C9*D9) =F9/E9
SQL - Calculate cumulative average cost price for a product
First we need to find a query to give us the current stock level for each product. For that, we left join
purchaseorderlineandorderlinetoproductand calculate the differences for each row. Since a product can be in multiple orders we additionally aggregate the result, to get the overall difference -- the current stock level -- for each product.SELECT p.productid,
p.productname,
sum(coalesce(pol.qty, 0) - coalesce(ol.qty, 0)) qty
FROM product p
LEFT JOIN purchaseorderline pol
ON pol.productid = p.productid
LEFT JOIN orderline ol
ON ol.productid = p.productid
GROUP BY p.productid,
p.productname;Next we need the quantity, that was stocked for each product and day (of
purchaseorders). To get that, we inner joinpurchaseorderandpurchaseorderline. Again we aggregate to account for the possible case, that multiple orders on the same day were made for the same product.SELECT pol.productid,
po.purchaseorderdate,
sum(pol.qty) qty,
sum(pol.qty * pol.unitprice) unitprice
FROM purchaseorder po
INNER JOIN purchaseorderline pol
ON pol.purchaseorderid = po.purchaseorderid
GROUP BY pol.productid,
po.purchaseorderdate;We can now use the previous result and window functions to get the sum of the quantity stocked and the average price of the products up to each day.
SELECT po.productid,
po.purchaseorderdate,
sum(po.qty) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate) qty,
sum(po.unitprice) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate)
/
sum(po.qty) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate) unitprice
FROM (SELECT pol.productid,
po.purchaseorderdate,
sum(pol.qty) qty,
sum(pol.qty * pol.unitprice) unitprice
FROM purchaseorder po
INNER JOIN purchaseorderline pol
ON pol.purchaseorderid = po.purchaseorderid
GROUP BY pol.productid,
po.purchaseorderdate) po;
Now we put the results from 1. and 2. together using OUTER APPLY. For each product, we select the TOP 1 result from 2. ordered by the day descending -- i.e. younger orders first --, that stocked a quantity greater than or equal to the one currently in stock.
SELECT p.productid,
p.productname,
po.unitprice
FROM (SELECT p.productid,
p.productname,
sum(coalesce(pol.qty, 0) - coalesce(ol.qty, 0)) qty
FROM product p
LEFT JOIN purchaseorderline pol
ON pol.productid = p.productid
LEFT JOIN orderline ol
ON ol.productid = p.productid
GROUP BY p.productid,
p.productname) p
OUTER APPLY (SELECT TOP 1
po.unitprice
FROM (SELECT po.productid,
po.purchaseorderdate,
sum(po.qty) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate) qty,
sum(po.unitprice) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate)
/
sum(po.qty) OVER (PARTITION BY po.productid
ORDER BY po.purchaseorderdate) unitprice
FROM (SELECT pol.productid,
po.purchaseorderdate,
sum(pol.qty) qty,
sum(pol.qty * pol.unitprice) unitprice
FROM purchaseorder po
INNER JOIN purchaseorderline pol
ON pol.purchaseorderid = po.purchaseorderid
GROUP BY pol.productid,
po.purchaseorderdate) po) po
WHERE po.productid = p.productid
AND po.qty >= p.qty
ORDER BY po.purchaseorderdate DESC) po;
Calculation average costing using with recursive sql (postgres 9.1)
This is probably not the "best" answer to your question, but while struggling with this tricky problem, I hit - just by accident - some ugly workaround :).
Click on this SQL Fiddle demo
with recursive
trans (n, id, amt, qty, oqty, sqty, prevavg, avg) as (
select 0 n, id, amt, qty, oqty, sqty, avg, avg
from runsum
union
select trans.n + 1, runsum.id, trans.amt, trans.qty, trans.oqty, trans.sqty,
lag(trans.avg) over (order by 1),
case when runsum.sqty=0 then runsum.amt
else
((trans.prevavg *(runsum.sqty+trans.qty))-(runsum.amt*trans.qty)+(trans.prevavg*trans.oqty))/(runsum.sqty+trans.oqty)
end
from runsum
join trans using (id)
where trans.n < 20
)
select *
from trans
where prevavg is null and avg is not null
order by id;
It seems that the source of the problem is UNION clause in the recursive query.
Read this link: http://www.postgresql.org/docs/8.4/static/queries-with.html
They wrote that for UNION the recursive query discards duplicate rows while evaluating recursive query.
Perpetual Weighted Average cost Calculation SQL Server 2008
If I understand correctly, you want the cumulative average price.
This approach uses subqueries to calculate the cumulative total quantity and the cumulative total paid. The ratio is the avg cost:
select t.*, cumepaid / cumeqty as avg_cost
from (select t.*,
(select SUM(qty) from t t2 where t2.item_id = t.item_id and t2.purch_id <= t.purch_id
) as cumeqty,
(select SUM(qty*unit_price) from t t2 where t2.item_id = t.item_id and t2.purch_id <= t.purch_id
) as cumepaid
from t
) t
In SQL Server 2012, you can do this by directly calculating cumulative sums (should be more efficient). You could also do this with cross apply, but I prefer standard SQL.
Calculate Weighted Cost (Avg) of a product in T-SQL
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
Calculate Average Qty On Hand of Inventory
I was able to solve this with a sum. First, I multiplied the final quantity on hand by the number of days in the range. Next, I multiplied each change in inventory by the time from @StartDate until the TransDate.
select
[Part].[PartNum] as [Part_PartNum],
(max(PartWhse.OnHandQty)*datediff(day,@StartDate,Constants.Today)-
sum(PartTran.TranQty*datediff(day,@StartDate,PartTran.TranDate))) as [Calculated_WeightedSum],
(WeightedSum/DATEDIFF(day, @StartDate, Constants.Today)) as [Calculated_AverageOnHand]
from Erp.Part as Part
right outer join Erp.PartTran as PartTran on
Part.PartNum = PartTran.PartNum
inner join Erp.PartWhse as PartWhse on
Part.PartNum = PartWhse.PartNum
group by [Part].[PartNum]
Thanks for your help everyone! You really helped me think it through.
Related Topics
What Is the Fastest Way to Truncate Timestamps to 5 Minutes in Postgres
SQL Row_Number() Function in Where Clause Without Order By
Truncate All Tables in MySQL Database That Match a Name Pattern
Select from One Table, Insert into Another Table Oracle SQL Query
How to Insert Data into Table Using Stored Procedures in Postgresql
How to Keep the Order Using Select Where In()
How to Implement a Substring Search in SQL
Get All Characters Before Space in MySQL
Delete with "Join" in Oracle SQL Query
Comparing Results with Today's Date
How to Format Bigint Field into a Date in Postgresql
Need SQL Query to Find Parent Records Without Child Records
Unit Testing Framework for Oracle Pl/Sql
How to Sort a Linked List in SQL
SQL Statement with Multiple Sets and Wheres
How to Count Number of Occurrences for All Different Values in Database Column
Oracle: SQL Query That Returns Rows with Only Numeric Values