Calculate Profit Based on First-In, First-Out Pricing By Date Of Sale

How can I find the sales margin every Day via SQL, assuming they are sold in the order they were purchased?

Answer

Please try this solution –

;with cte as 
(
select purchase_date,item,cost, qty as num from purchase
union all
select purchase_date,item,cost, num-1 from cte where num>1
), 
cte2 as
(
select sale_date,item,price, qty as num from sales
union all
select sale_date,item,price, num-1 from cte2 where num>1
)

select sale_date, sum(price-cost) from (

(select sale_date, item, price ,row_number() over (order by sale_date,num) rn from cte2) s
inner join
 (select purchase_date, item, cost ,row_number() over (order by purchase_date,num) rn2 from cte) z
 on s.item=z.item and s.rn=z.rn2)  

group by sale_date