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.

Agreement Number GCBF Transaction Type Tranasction Type Transaction Date Days Return of Capital Interest Payment Amount
830065 Add Drawdown 02/06/2019 0 0 0 75000
830065 Direct debit Interest Payment 05/06/2019 NULL 0 676.64 676.64
830065 Reschedule Drawdown 05/08/2019 0 0 0 112799
830065 Direct debit Interest Payment 08/06/2019 92 0 1728.55 1728.55
830065 Reschedule Drawdown 09/08/2019 0 0 0 60299
830065 Direct debit Interest Payment 06/11/2019 92 0 2295.43 2295.43
830065 Reschedule Drawdown 06/11/2019 0 0 0 50814
830065 Direct debit Interest Payment 02/06/2020 92 0 2787.66 2787.66
830065 Direct debit Interest Payment 05/06/2020 90 0 2727.06 2727.06
830065 Direct debit Interest Payment 08/06/2020 92 0 2787.66 2787.66
830065 Direct debit Return of Capital 06/11/2020 92 23645.64 2787.66 23645.64
830065 Direct debit Interest Payment 06/11/2020 0 23645.64 2787.66 2787.66
830065 Unpaid DD – Instr Cancelled Unpaid Return of Capital 06/11/2020 0 -23645.64 -2787.66 -23645.64
830065 Unpaid DD – Instr Cancelled Unpaid Interest Payment 06/11/2020 0 -23645.64 -2787.66 -2787.66
830065 cash receipt Return of Capital 02/15/2021 101 23645.64 2787.66 23645.64
830065 cash receipt Interest Payment 02/15/2021 0 23645.64 2787.66 2787.66
830065 cash receipt Return of Capital 02/15/2021 0 23866.16 2567.14 23866.16
830065 cash receipt Interest Payment 02/15/2021 0 23866.16 2567.14 2567.14
830065 cash receipt Return of Capital 07/04/2021 51 23645.64 2787.66 23645.64
830065 cash receipt Interest Payment 07/04/2021 0 23645.64 2787.66 2787.66
830065 cash receipt Return of Capital 05/17/2021 40 23997.81 2435.49 23997.81
830065 cash receipt Interest Payment 05/17/2021 0 23997.81 2435.49 2435.49
830065 Direct debit Return of Capital 08/06/2021 81 24283.72 2149.58 24283.72
830065 Direct debit Interest Payment 08/06/2021 0 24283.72 2149.58 2149.58
830065 Direct debit Return of Capital 06/11/2021 92 24536.95 1896.35 24536.95
830065 Direct debit Interest Payment 06/11/2021 0 24536.95 1896.35 1896.35
830065 Direct debit Return of Capital 02/06/2022 92 24540.86 1892.44 24540.86
830065 Direct debit Interest Payment 02/06/2022 0 24540.86 1892.44 1892.44
830065 Unpaid DD – Refer To Payer Unpaid Return of Capital 02/07/2022 1 -24540.86 -1892.44 -24540.86
830065 Unpaid DD – Refer To Payer Unpaid Interest Payment 02/07/2022 0 -24540.86 -1892.44 -1892.44
830065 Recalc NULL 02/08/2022 0 0 0 -8.01
830065 cash receipt Return of Capital 02/09/2022 2 -24799.72 -1633.58 -24799.72
830065 cash receipt Interest Payment 02/09/2022 0 -24799.72 -1633.58 -1633.58
830065 cash receipt Return of Capital 02/09/2022 0 24540.86 1892.44 24540.86
830065 cash receipt Interest Payment 02/09/2022 0 24540.86 1892.44 1892.44
830065 cash receipt Return of Capital 02/09/2022 0 24769.72 1663.58 24769.72
830065 cash receipt Interest Payment 02/09/2022 0 24769.72 1663.58 1663.58
830065 Direct debit Return of Capital 05/06/2022 86 26433.3 0 26433.3

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

Leave a Comment