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