sql server – how to get balance using SQL using condition

Below is my SQL i’m using and I want to calculate the Running Balance

For Example

I awant to add the opening balance column which will be the be the closing balance of the previous quarter.I will then the current Quarter balance as calculating:

OpeningBalance Previous Quarter + (Drawdown) – (Unpaid%+Refund+Return of Capital) = ClosingBalance below is the Business Rule

1. If the Transaction Type is Drawdown It will add in the Current balance. 2.If Customer has made any Return of Capital which mean Customer contributing capital with Interest, only Return of Capital will reduce from Balance.
2. Check if there is any unpaid Return this will increase the balance as the payment is not clered
3. CHeck if there is any Refund this will increase the balance.

Below is the SQL Script I use

Select 

 RIGHT(AgreementNumber, LEN(AgreementNumber) - 4)+ ''  AS 'Agreement Number'
,ISNULL(CustomerCompany,'') + '_' + RIGHT(AgreementNumber, LEN(AgreementNumber) - 4) + ''  AS 'Investment Name'
,TransactionTypeTable.TransTypeDescription as 'GCBF Transaction Type'
,CASE 

WHEN TransactionTypeTable.TransTypeDescription  = 'Direct Debit' AND V.AmountType="Principle" THEN 'Return of Capital'
WHEN TransactionTypeTable.TransTypeDescription  = 'Direct Debit' AND V.AmountType="Interest" THEN 'Interest Payment'
WHEN TransactionTypeTable.TransTypeDescription  = 'Unpaid DD - No Instruction' AND V.AmountType="Principle" THEN 'Unpaid Return of Capital'
WHEN TransactionTypeTable.TransTypeDescription  = 'Unpaid DD - No Instruction' AND V.AmountType="Interest" THEN 'Unpaid Interest Payment'
WHEN TransactionTypeTable.TransTypeDescription  = 'Unpaid DD - Refer To Payer' AND V.AmountType="Principle" THEN 'Unpaid Return of Capital'
WHEN TransactionTypeTable.TransTypeDescription  = 'Unpaid DD - Refer To Payer' AND V.AmountType="Interest" THEN 'Unpaid Interest Payment'
WHEN TransactionTypeTable.TransTypeDescription  ='Unpaid DD - Instr Cancelled' AND V.AmountType="Interest" THEN 'Unpaid Interest Payment'
WHEN TransactionTypeTable.TransTypeDescription  ='Unpaid DD - Instr Cancelled' AND V.AmountType="Principle" THEN 'Unpaid Return of Capital'

WHEN TransactionTypeTable.TransTypeDescription  = 'Refund' AND V.AmountType="Principle" THEN 'Return of Capital'
WHEN TransactionTypeTable.TransTypeDescription  = 'Refund' AND V.AmountType="Interest" THEN 'Interest Payment'
WHEN TransactionTypeTable.TransTypeDescription  = 'Cash receipt' AND V.AmountType="Principle" THEN 'Return of Capital'
WHEN TransactionTypeTable.TransTypeDescription  = 'Cash receipt' AND V.AmountType="Interest" THEN 'Interest Payment'
WHEN TransactionTypeTable.TransTypeDescription  = 'Interest Adjustment' AND V.AmountType="Interest" THEN 'Interest Adjustment' 
WHEN TransactionTypeTable.TransTypeDescription  = 'Interest Adjustment' AND V.AmountType="Principle" THEN 'Capital Adjustment' 

END AS 'Tranasction Type'
,TransDate as 'Transaction Date'
,datediff(day,lag(TransDate,1) over (partition by TransAgreementNumber order by TransDate asc),TransDate) as Days
,TransNetPrincipal as 'Return of Capital'
,TransNetInterest as 'Interest Payment',V.Amount as Amount

From AgreementTable
   
inner join TransactionTable  on TransactionTable.TransAgreementNumber = AgreementTable.AgreementNumber
inner join CustomerTable on AgreementTable.AgreementCustomerNumber= CustomerTable.CustomerNumber
inner join TransactionTypeTable on TransactionTypeTable.TransTypeID = TransactionTable.TransTypeID

CROSS APPLY (VALUES('Principle',TransNetPrincipal ),
                        ('Interest',TransNetInterest ))V(AmountType,Amount)

where AgreementNumber like 'IUKL%' and TransTypeDescription in (

'Direct Debit' ,
'Unpaid DD - No Instruction' ,
'Unpaid DD - Refer To Payer',
'Unpaid DD - Instr Cancelled',
'Refund' ,
'Cash receipt', 
'Interest Adjustment') 
and  V.Amount != 0 

union all
--- Drawdown i-level format--
select  
RIGHT(AgreementNumber, LEN(AgreementNumber) - 4)+ ''  AS 'Agreement Number'
,ISNULL(CustomerCompany,'') + '_' + RIGHT(AgreementNumber, LEN(AgreementNumber) - 4) + ''  AS 'Investment Name'

,AuditAgreementAction as 'GCBF Transaction Type'
,CASE  WHEN AuditAgreementAction in( 'Add' ,'Reschedule') then 'Drawdown' else null end as 'Transaction Type'
,AuditAgreementDate  As 'Transaction Date'
,'' as Day
,'' as 'Return of Capital'
,'' as 'Interest Payment'
,AuditAgreementPrincipal as 'Amount'

from [dbo].[AuditAgreementTable]
left join AgreementTable on AgreementTable.AgreementNumber= AuditAgreementTable.AuditAgreementAgreementNumber
left join CustomerTable on AgreementTable.AgreementCustomerNumber= CustomerTable.CustomerNumber
where AuditAgreementAgreementNumber like 'IUKL%' and AuditAgreementPrincipal not like '0.00' and AgreementAutoStatus like 'Live%' and AuditAgreementAction not in ('Amend')
 --and  AuditAgreementDate  between '2021-01-05' and '2021-30-05' 
 order by 'Agreement Number','Transaction Date'   asc ```