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
computed.

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

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

    RETURN @Balance    
END    
GO 

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))
 ) 
GO 

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

SELECT 
    AccountNumber 
    ,TransactionDate 
    ,TransactionCode 
    ,Amount 
    ,Balance 
FROM AccountRegister 
ORDER BY AccountNumber, TransactionDate 

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