SQL Help--Iterating Data within in the Query

[v4varun]

Hi All,

I have below Scenario where Derived Column 1 and Derived Column 2 are expected Output.(rest are all in same table)
For Each Debit need to check if Credit is able to satisfy that value if not, it will satisfy the partial transaction got to next for rest of the amount(Shown in Trans_id=101)
Similarly if Credit is higher and able to satisfy the debit it will go to next debit for transaction(Shown in Trans_id=102)
Total of Credit -debit will always be zero.
Normally each transaction have only 2 record of credit and debit but few cases there are multiple which need to be handled

Looking to write it in SQL Query.I understand that it might be possible in stored procedure or function but cannot use it due to some constraints

I understand its a looping concept and can be done in procedural language but if any one can give any idea to do it in SQL

Attached is the Screen shot of the Scenarios and below as well

TRAN_DT TRAN_ID Amount transaction Transaction Type Derived Column1 Derived Column2
2-Jul-21 101 0.5 Deposit Credit Deposit-Cash In 0.5 270-0.5=269.5
2-Jul-21 101 100 Deposit Credit Deposit-Cash In 100 269.5-100=169.5
2-Jul-21 101 150 Deposit Credit Deposit-Cash In 150 169.5-150=19.5
2-Jul-21 101 152 Deposit Credit Deposit-Cash In 19.5 152-19.5=132.5
2-Jul-21 101 270 Cash In Debit Deposit-Check 0.5 132.5-0.5=132
2-Jul-21 101 0.5 Check Debit Deposit-Check 60 132-60=72
2-Jul-21 101 60 Check Debit Deposit-Check 72 72-72=0
2-Jul-21 101 72 Check Debit
3-Jul-21 102 3551 Deposit Credit Deposit-Cash In 1000 3551-1000=2551
3-Jul-21 102 1000 Cash In Debit Deposit-Check 41 2551-41=2510
3-Jul-21 102 41 Check Debit Deposit-Check 225 2510-225=2285
3-Jul-21 102 225 Check Debit Deposit-Check 285 2285-285=2000
3-Jul-21 102 285 Check Debit Deposit-Check 2000 2000-2000=0
3-Jul-21 102 2000 Check Debit
4-Jul-21 103 3000 Deposit Credit Deposit-Cash In 3000
4-Jul-21 103 3000 CaSh IN Debit

[skhanal]

You can't do such logic in a single SQL Statement. If it was recursion then you could do WITH statement in SQL Server.