Solution to TSQL Challenge 39 - Calculate Gross Profit by Item using FIFO method By v_siva_v

select Item,Sold#,Cost$+((Sold#-cnt)*costdiff) as Cost$,Sales$,Sales$-(Cost$+((Sold#-cnt)*costdiff)) as Profit
from 

(
 select distinct p.ItemNumber as [Item],s.QTY as [Sold#],s.SoldPrice as [Sales$],SUM(case when s.QTY >= p.QTY then  p.Quantity end) as cnt,
 
 sum(case when s.QTY >= p.QTY then  p.Quantity*p.Cost end ) as [Cost$],MIN(case when  s.QTY < p.QTY  then p.Cost end) as costdiff
 
 from (   Select PurchaseID,PurchaseDate,ItemNumber,Quantity,Cost,(select sum(Quantity) from TC39_Purchases p1 where p1.ItemNumber=p.ItemNumber 
                                                                    and p1.PurchaseDate <= p.PurchaseDate
                                                                    group by p1.ItemNumber) as QTY 
          from TC39_Purchases p) p
 inner join (   select ItemNumber,sum(Quantity) as QTY,SUM(quantity*price) as SoldPrice
   from TC39_Sales  group by ItemNumber) s
 on p.ItemNumber=s.ItemNumber
 
 group by p.ItemNumber ,s.QTY ,s.SoldPrice 
 
 ) sp

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Dec 30 2010  1:56PM      746288         538       15678   15.225
  2 Dec 30 2010  3:43PM      746292         538       17004   15.423
  3 Dec 30 2010  5:32PM      746288         538       17222   15.542
  4 Dec 30 2010  7:20PM      746288         538       17191   15.771
  5 Dec 30 2010  9:08PM      746288         538       17737   15.224