Calculate Profit Based on First-In, First-Out Pricing
mysql, sql, sql-server
Solution
Good question. The approach that I'm taking is to calculate the total sales. Then calculate cumulative purchases, and combine them with special logic to get the right arithmetic for the combination:
select s.sku,
(MarginPos - SUM(case when s.totalqty < p.cumeqty - p.qty then p.price * p.qty
when s.totalqty between p.cumeqty - p.qty and p.qty
then s.price * (s.totalqty - (p.cumeqty - p.qty))
else 0
end)
) as Margin
from (select s.sku, SUM(price*qty) as MarginPos, SUM(qty) as totalqty
from sales s
) s left outer join
(select p.*,
(select SUM(p.qty) from purchase p2 where p2.sku = p.sku and p2.sale_id <= p.sale_id
) as cumeqty
from purchase s
)
on s.sku = p.sku
group by s.sku, MarginPos
Note: I haven't tested this query so it might have syntax errors.
Problem
Say I have purchase and sales data for some SKUs: ``` po_id | sku | purchase_date | price | qty ---------------------------------------------- 1 | 123 | 2013-01-01 12:25 | 20.15 | 5 2 | 123 | 2013-05-01 15:45 | 17.50 | 3 3 | 123 | 2013-05-02 12:00 | 15.00 | 1 4 | 456 | 2013-06-10 16:00 | 60.00 | 7 sale_id | sku | sale_date | price | qty ------------------------------------------------ 1 | 123 | 2013-01-15 11:00 | 30.00 | 1 2 | 123 | 2013-01-20 14:00 | 28.00 | 3 3 | 123 | 2013-05-10 15:00 | 25.00 | 2 4 | 456 | 2013-06-11 12:00 | 80.00 | 1 ``` How can I find the sales margin via SQL, assuming they are sold in the order they were purchased? E.g, the margin for sku 123 is ``` 30*1 + 28*3 + 25*2 - 20.15*5 - 17.50*1 ``` with 2 purchased at 17.50 and 1 purchased at 15.00 left unsold.