Calculating Balance based on the transaction

Question

Post reply

Calculating Balance based on the transaction

yogesh_pandey

SSC Veteran

Points: 204
More actions
October 6, 2010 at 11:56 am

#241374

I have two tables that stores balance and transaction information about an account.
The first table stores all the transaction with transaction amount and effective date. The transaction has a code that differentiates deposit or withdrawal.
The other table stores balance but this balance is not historical. It is a rolling balance. It keeps on updating every time there is a transaction.
There is no place that stores the historical balance information after each transaction.
How do I calculate balance after each transaction in the history table using the current balance and the transaction code? Below is the example…
This is how the balance is stored
acct_no acct_type effective_dt cur_bal
112233 SV 9/4/2003 181447.83
This is how the transaction is stored.
acct_no acct_type effective_dt tran_code amt
112233 SV 12/31/2003 110 100.16
112233 SV 1/6/2004 101 850
112233 SV 1/20/2004 101 1400
112233 SV 1/31/2004 110 105.58
112233 SV 2/2/2004 101 284
112233 SV 2/10/2004 101 1000
112233 SV 2/17/2004 101 600
112233 SV 2/29/2004 110 104.7
112233 SV 3/2/2004 101 400
112233 SV 3/29/2004 101 1200
112233 SV 3/31/2004 110 114.2
112233 SV 4/12/2004 101 700
I want to able to assign the end balance after each transaction based on the current balance. The row with the max date gets the cur balance and then you should be able to work back in time. Any transaction with Tran code of 110 I want to add to the cur bal and any 101 I want to subtract. How do I loop through or what can I do to achieve the result like below?
acct_no acct_type effective_dt tran_code amt Balance
112233 SV 12/31/2003 110 100.16 176138.5
112233 SV 1/6/2004 101 850 176038.3
112233 SV 1/20/2004 101 1400 176888.3
112233 SV 1/31/2004 110 105.58 178288.3
112233 SV 2/2/2004 101 284 178182.7
112233 SV 2/10/2004 101 1000 178466.7
112233 SV 2/17/2004 101 600 179466.7
112233 SV 2/29/2004 110 104.7 180066.7
112233 SV 3/2/2004 101 400 179962
112233 SV 3/29/2004 101 1200 180362
112233 SV 3/31/2004 110 114.2 181562
112233 SV 4/12/2004 101 700 181447.8
-----------------------
Below is the DDL
---For current balance
CREATE TABLE #Display
(
acct_no int,
acct_type char(3),
effective_dt datetime,
cur_bal decimal(18,2)
)
INSERT INTO #Display
(
acct_no,
acct_type,
effective_dt,
cur_bal
)
(
SELECT '112233' as acct_no,'SV' as acct_type,'9/4/2003' as effective_dt,'181447.83' as cur_bal
)
----For transactions
CREATE TABLE #Transaction
(
acct_no int,
acct_type char(3),
effective_dt datetime,
tran_code int,
amt decimal(18,2)
)
INSERT INTO #Transaction
(
acct_no,
acct_type,
effective_dt,
tran_code,
amt
)
(
SELECT '112233','SV','12/31/2003','110','100.16' UNION ALL
SELECT '112233','SV','1/6/2004','101','850' UNION ALL
SELECT '112233','SV','1/20/2004','101','1400' UNION ALL
SELECT '112233','SV','1/31/2004','110','105.58' UNION ALL
SELECT '112233','SV','2/2/2004','101','284' UNION ALL
SELECT '112233','SV','2/10/2004','101','1000' UNION ALL
SELECT '112233','SV','2/17/2004','101','600' UNION ALL
SELECT '112233','SV','2/29/2004','110','104.7' UNION ALL
SELECT '112233','SV','3/2/2004','101','400' UNION ALL
SELECT '112233','SV','3/29/2004','101','1200' UNION ALL
SELECT '112233','SV','3/31/2004','110','114.2' UNION ALL
SELECT '112233','SV','4/12/2004','101','700'
)