# Get the updated value from the previous line using the LAG Without using the recursive CTE

How to use LAG function to get the updated previous row value (without using Recursive CTE). Please check the screenshot for sample output

Query Tried

`````` 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 < 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 < 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
```
```

UPDATE: 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.

Recursive CTE Query:

``````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 < RP.TotalDebit Then R.Debit + (RP.TotalDebit-R.Credit) Else R.Debit End,
Amount = Case When R.Credit < 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
```
```

Performance here is suffering from recursive CTE. CTE on it's own is just syntactic sugar.

Just for this particular sample data this works without recursion:

``````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 CTE1 As (
Select *
, CASE WHEN Credit > 0 THEN LEAD(1 - SIGN(Credit), 1, 1) OVER (ORDER BY SNO) ELSE 0 END As LastCrPerBlock
From @Tbl
), CTE2 As (
Select *
, SUM(LastCrPerBlock) OVER (ORDER BY SNO DESC ROWS UNBOUNDED PRECEDING) As BlockNumber
From CTE1
), CTE3 As (
Select *
, SUM(Credit - Debit) OVER (PARTITION BY BlockNumber) As BlockTotal
, SUM(Credit - Debit) OVER (PARTITION BY BlockNumber ORDER BY SNO ROWS UNBOUNDED PRECEDING) As BlockRunningTotal
From CTE2
)
Select SNO, Credit, Debit
, CASE WHEN BlockRunningTotal < 0 THEN -BlockRunningTotal ELSE 0 END As TotalDebit
, CASE WHEN BlockRunningTotal > 0 THEN CASE WHEN Credit < BlockRunningTotal THEN Credit ELSE BlockRunningTotal END ELSE 0 END As Amount
, PaidDate
From CTE3
Order By SNO;
```
```

This can help evaluate performance, but it will fail if in any block total of `Debit`s exceed total of `Credit`s. If `BlockTotal` is negative then it must be merged with one or several following blocks and that can't be done without iteration or recursion.

In real life I would dump CTE3 into temporary table and cycle over it merging blocks until there are no more negative `BlockTotal`s.