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

Using a Computed Column to Calculate a Running Total

One common question I get is "How can a calculate a running total for a column in a table?"

Typically, this was done with a query using a self-join on the table. While this is a perfectly valid way to accomplish the goal, SQL Server 2005 added a new feature that makes it simpler to implement, executes much faster, and you only have to do it once!

To demonstrate these two techniques, we first need a table and some data:


Using a Self-Join Query

The typical way this was done in the past was to write a query that joined the table back to itself and used a SUM aggregate with a GROUP BY clause to calculate the running total at query time.

This will give the following resultset:







Using a Computed Column

SQL Server 2005 introduced a new feature called a computed column. This can be used to define a column as an expression instead of specifying a data type. Normally, this expression will only reference data in the same row to compute its value. Part of this feature that is often overlooked, is that you can use a User Defined Scalar Function call as the expression to calculate the columns value. We will take advantage of that to calculate the running totals.

The first thing we need to implement this is a function that will take a datetime parameter and calculate the running total for all rows with a datetime less than or equal to the value passed to the function.

Note: It should be obvious that to use this method, the column chosen must be unique and specify the order of the rows for the calculation.

The Function














To use this function, we need to alter the definition of the table we created earlier to add the computed column. The table script will now look like this:

Note the definition of the RunningTotal column.

Since the running totals are now calculated for us by the table definition itself, to get the same result set we can use this much simpler query:

The new Computed Column feature in SQL Server 2005 can be used in many other ways. To explore this useful tool more fully, please refer to:

http://msdn.microsoft.com/en-us/library/ms191250.aspx

Well, I have been thinking about this for a long time and finally decided to bite the proverbial bullet and join the on-line realm of blogging. Once the decision was made, the subject matter was a forgone conclusion. It had to be about my career and favorite passtime, databases and Business Intelligence of course :-)