title: FIFO Stock Valuation Through CTE-Recursion
body: <p>I have copied it from this site because it's been already closed but I needed it for further solution. thus, kindly help me out.....</p>
  
  <p>Problem :  it's calculating the closing stock valuation through FIFO of issue as a whole. but i need cost of issues  into Price column in the same row it belongs to itself.</p>
  
  <pre><code>declare @Stock table (Item char(3) not null,[Date] datetime not null,TxnType varchar(3) not null,Qty int not null,Price decimal(10,2) null)
  insert into @Stock(Item ,  [Date] ,        TxnType, Qty,  Price) values
  ('ABC','20120401','IN',    200, 750.00),
  ('ABC','20120405','OUT',   100 ,null  ),
  ('ABC','20120410','IN',     50, 700.00),
  ('ABC','20120416','IN',     75, 800.00),
  ('ABC','20120425','OUT',   175, null  ),
  ('XYZ','20120402','IN',    150, 350.00),
  ('XYZ','20120408','OUT',   120 ,null  ),
  ('XYZ','20120412','OUT',    10 ,null  ),
  ('XYZ','20120424','IN',     90, 340.00);
  
  ;WITH OrderedIn as (
      select *,ROW_NUMBER() OVER (PARTITION BY Item ORDER BY [DATE]) as rn
      from @Stock
      where TxnType = 'IN'
  ), RunningTotals as (
      select Item,Qty,Price,Qty as Total,0 as PrevTotal,rn from OrderedIn where rn = 1
      union all
      select rt.Item,oi.Qty,oi.Price,rt.Total + oi.Qty,rt.Total,oi.rn
      from
          RunningTotals rt
              inner join
          OrderedIn oi
              on
                  rt.Item = oi.Item and
                  rt.rn = oi.rn - 1
  ), TotalOut as (
      select Item,SUM(Qty) as Qty from @Stock where TxnType='OUT' group by Item
  )
  select
      rt.Item,SUM(CASE WHEN PrevTotal &gt; out.Qty THEN rt.Qty ELSE rt.Total - out.Qty END * Price)
  from
      RunningTotals rt
          inner join
      TotalOut out
          on
              rt.Item = out.Item
  where
      rt.Total &gt; out.Qty
  group by rt.Item
  </code></pre>
  
  <p>The  result is only Closing Stock as per FIFO Basis as below:
  Item    ClsStock
  ABC 40000.00
  XYZ 37600.00</p>
  
  <p>But I need the followings Result from the same query:</p>
  
  <p>Item   Date TxnType OpnQty  OpnRate OpnVal  InnQty  InnRate InnVal  OutQty  OutRate OutVal  ClsQty  ClsRate ClsVal</p>
  
  <p>ABC 20120401    IN              200 750 150000              200 750.00  150000</p>
  
  <p>ABC 20120405    OUT 200 750.00  150000              100 750 75000   100 750.00  75000</p>
  
  <p>ABC 20120410    IN  100 750.00  75000   50  700 35000               150 733.33  110000</p>
  
  <p>ABC 20120416    IN  150 733.33  110000  75  800 60000               225 755.56  170000</p>
  
  <p>ABC 20120425    OUT 225 755.56  170000              175 742.86  130000  50  800.00  40000</p>
  
  <p>Total           0   0.00    0   325 753.85  245000  275 745.45  205000  50  800.00  40000</p>
  
  <p>XYZ 20120402    IN              150 350 52500               150 350.00  52500</p>
  
  <p>XYZ 20120408    OUT 150 350.00  52500               120 350 42000   30  350.00  10500</p>
  
  <p>XYZ 20120412    OUT 30  350.00  10500               10  350 3500    20  350.00  7000</p>
  
  <p>XYZ 20120424    IN  20  350.00  7000    90  340 30600               110 341.82  37600</p>
  
  <p>Total           0   0.00    0   240 346.25  83100   130 350 45500   110 341.82  37600</p>
  
  <p>Grand Total         0   #DIV/0! 0   565 580.71  328100  405 618.52  250500  160 485.00  77600</p>