title: Get previous row updated value using LAG Without using Recursive CTE
body: <p>How to use <strong>LAG</strong> function to get the updated previous row value (without using <strong>Recursive CTE</strong>). Please check the screenshot for sample output</p>
  
  <p><a href="https://i.stack.imgur.com/DLreL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DLreL.png" alt="enter image description here"></a></p>
  
  <p><strong>Query Tried</strong></p>
  
  <pre><code> Declare @Tbl as Table(SNO Int,Credit Money,Debit Money,PaidDate Date)
  Insert into @Tbl
  SELECT * FROM (VALUES (1,0,12,'7Jan16'), (2,10,0,'6Jan16'), (3,15,0,'5Jan16'), (4,0,5,'4Jan16'), (5,0,3,'3Jan16'), (6,0,2,'2Jan16'), (7,20,0,'1Jan16')) AS X(SNO,Credit,Debit,PaidDate)
  
  Select
      T.SNO,
      T.Credit,
      T.Debit,
      TotalDebit = Case When Credit &lt; LAG(T.Debit, 1, 0) OVER (ORDER BY SNO) Then Debit + (LAG(T.Debit, 1, 0) OVER (ORDER BY SNO)-Credit) Else Debit End,
      Amount = Case When Credit &lt; LAG(T.Debit, 1, 0) OVER (ORDER BY SNO) Then 0 Else Credit-LAG(T.Debit, 1, 0) OVER (ORDER BY SNO) End,
      T.PaidDate
  From @Tbl T
  </code></pre>
  
  <h2><a href="https://i.stack.imgur.com/59Ksf.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/59Ksf.png" alt="enter image description here"></a></h2>
  
  <p><strong>UPDATE:</strong>
  Can get the expected result using recursive CTE, but when i convert the query to function and when i join the function with 3000 record, takes long time to execute. That's why i am trying to convert the query without recursive CTE part.</p>
  
  <p><strong>Recursive CTE Query:</strong></p>
  
  <pre><code>Declare @Tbl as Table(SNO Int,Credit Money,Debit Money,PaidDate Date)
  Insert into @Tbl
  SELECT * FROM (VALUES (1,0,12,'7Jan16'), (2,10,0,'6Jan16'), (3,15,0,'5Jan16'), (4,0,5,'4Jan16'), (5,0,3,'3Jan16'), (6,0,2,'2Jan16'), (7,20,0,'1Jan16')) AS X(SNO,Credit,Debit,PaidDate)
  
  
  ;With Temp As(/* Detect Debited amount */
      Select Top 1 SNO,Credit,Debit,Debit As TotalDebit,Credit As Amount,PaidDate From @Tbl
      Union All
      Select
          R.SNO,
          R.Credit,
          R.Debit,
          TotalDebit = Case When R.Credit &lt; RP.TotalDebit Then R.Debit + (RP.TotalDebit-R.Credit) Else R.Debit End,
          Amount = Case When R.Credit &lt; RP.TotalDebit Then 0 Else R.Credit-RP.TotalDebit End,
          R.PaidDate
      From @Tbl R
      Inner Join Temp RP ON R.SNO-1=RP.SNO
  )
  
  Select * From Temp
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/Z3Nn5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Z3Nn5.png" alt="enter image description here"></a></p>
  
  <p><strong>Spreadsheet sample:</strong> 
  <a href="https://docs.google.com/spreadsheets/d/1FNwzgGxmLiLFS_R5QANnfd16Iw64xhF0gWTc4ZocKsk/edit?usp=sharing" rel="nofollow noreferrer">https://docs.google.com/spreadsheets/d/1FNwzgGxmLiLFS_R5QANnfd16Iw64xhF0gWTc4ZocKsk/edit?usp=sharing</a></p>