Tuesday, August 5, 2008

Using a Computed Column to Maintain a Balance of Debits and Credits

A variation of the running total question I posted a solution for a few days ago (Using A Computed Column to Calculate a Running Total) that has come up from time to time over the years is how to compute a balance for an account register type table dealing with debits and credits. This is another area where SQL 2005's Computed columns can help. Here is the original table creation statement
CREATE TABLE AccountRegister
AccountNumber VARCHAR(16)
,TransactionDate Datetime
,TransactionCode CHAR(1)
,Amount NUMERIC (18,2)

Before we can add the computed column to the table, we have to develop the
function to compute a running balance for a given AccountNumber and
TransactionDate set of values. This is done by adding all debits TransactionCode
'D') and subtracting all Credits (TransactionCode 'C') for all rows with the
given AccountNumber and all TransactionDates <= to the TransactionDate being

CREATE FUNCTION dbo.CalculateDebitCreditBalance
@AccountNumber varchar(16)
,@TransactionDate Datetime
DECLARE @Balance NUMERIC (18,2)

SELECT @Balance = SUM(CASE TransactionCode
WHEN 'D' THEN Amount
WHEN 'C' THEN Amount * -1.0
FROM AccountRegister
WHERE TransactionDate <= @TransactionDate
AND AccountNumber = @AccountNumber

RETURN @Balance

Now we can create the table with the computed column using our newly developed function:

CREATE TABLE AccountRegister
AccountNumber VARCHAR(16)
,TransactionDate Datetime
,TransactionCode CHAR(1)
,Amount NUMERIC (18,2)
,Balance AS (dbo.CalculateDebitCreditBalance(AccountNumber, TransactionDate))

Lets insert some test data and verify that it computes correctly

INSERT INTO AccountRegister
SELECT '100-00034A','20070101','D', 100.0
UNION SELECT '100-00034A','20070102','D', 10.0
UNION SELECT '100-00034A','20070103','D', 10.0
UNION SELECT '100-00034A','20070104','C', 50.0
UNION SELECT '100-00035A','20070102','D', 150.0
UNION SELECT '100-00035A','20070104','D', 500.0
UNION SELECT '100-00035A','20070105','C', 250.0
UNION SELECT '100-00035A','20070106','D', 75.0
UNION SELECT '100-00035A','20070108','C', 50.0

Run the following query to test the computed column

FROM AccountRegister
ORDER BY AccountNumber, TransactionDate

Here are the results from executing the code in SQL Server Management Studio

No comments: