CODEWITHSUNDEEP
sqlsql-server
Mxo Sibeko
Mxo Sibeko

Reputation: 147

Calculating Closing and Opening Balance in SQL

I have a table 'transactions(txDate, amount, txType) where txType can "credit" or "debit". I need to get an opening and closing balance when returning transactions between two dates.

The first row of the results should be the Opening Balance, then a list of all tx between the dates and the last row to be a Closing balance

Getting the list isn't a train smash but for the balances, I currently have the following 

SELECT SUM(amount) AS [Opening Balance]
FROM 
(
    SELECT SUM([Amount]) amount
    FROM [dbo].[Transaction]
    WHERE [txDate] <= @startDate
    AND [txType] = 'credit'

    UNION ALL

    SELECT 0 - SUM([Amount]) amount
    FROM [dbo].[Transaction]
    WHERE [TransactionDate] <= @startDate
    AND [txType] = 'debit'
) Transactions

this gives a very big amount than what it should be.

And for the Closing balance, I have no idea how to go about it

Upvotes: 0

Views: 4420

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

You could use CASE in SUM

select  sum(case when txType = 'credit' and transactionDate <= @startDate 
                then amount end) - 
        sum(case when txType = 'debit' and transactionDate <= @startDate 
                then amount end)[Opening Balance],
        sum(case when txType = 'credit' and transactionDate <= @endDate 
                then amount end) - 
        sum(case when txType = 'debit' and transactionDate <= @endDate 
                then amount end)[Closing Balance]
from transaction

Upvotes: 2

Related Questions