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

Spreadsheet sample: https://docs.google.com/spreadsheets/d/1FNwzgGxmLiLFS_R5QANnfd16Iw64xhF0gWTc4ZocKsk/edit?usp=sharing

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 Debits exceed total of Credits. 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 BlockTotals.

more