Looping through the recordset to assign different balance value to each transaction row

Question

Post reply

Looping through the recordset to assign different balance value to each transaction row

yogesh_pandey

SSC Veteran

Points: 204
More actions
October 6, 2010 at 10:16 am

#230081

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_noacct_typeeffective_dtcur_bal
112233SV 9/4/2003181447.83
This is how the transaction is stored.
acct_noacct_typeeffective_dttran_codeamt
112233SV 12/31/2003110100.16
112233SV 1/6/2004101850
112233SV 1/20/20041011400
112233SV 1/31/2004110105.58
112233SV 2/2/2004101284
112233SV 2/10/20041011000
112233SV 2/17/2004101600
112233SV 2/29/2004110104.7
112233SV 3/2/2004101400
112233SV 3/29/20041011200
112233SV 3/31/2004110114.2
112233SV 4/12/2004101700
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_noacct_typeeffective_dttran_codeamtBalance
112233SV 12/31/2003110100.16176138.5
112233SV 1/6/2004101850176038.3
112233SV 1/20/20041011400176888.3
112233SV 1/31/2004110105.58178288.3
112233SV 2/2/2004101284178182.7
112233SV 2/10/20041011000178466.7
112233SV 2/17/2004101600179466.7
112233SV 2/29/2004110104.7180066.7
112233SV 3/2/2004101400179962
112233SV 3/29/20041011200180362
112233SV 3/31/2004110114.2181562
112233SV 4/12/2004101700181447.8

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

what you are after is a "Running Total" type of query;

what you end up doing is join the table to itself with an alias , by offsetting one row to the next;

so you need to be able to join row 1 to row 2;

you do that with row_number() usually;

something like this as an example:

SELECT a.cur_bal + b.cur_bal

from a

left outer join b

on a.ID = B.ID + 1

here's your data formatted as consumable data for others to play with:

SELECT '112233' as acct_no,'SV' as acct_type,'9/4/2003' as effective_dt,'181447.83' as cur_bal UNION ALL

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'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yogesh_pandey SSC Veteran Points: 204 More actions · Answer 2

Thank you for the reply. But the cur balance is in one table and the transaction is in another. I would have to use cur balance and remember the previous cur balance to calculate cur_bal for the previous transaction as I go up the list. I don't know if i make any sense. Please let me know.

Lowell SSC Guru Points: 323518 More actions · Answer 3

yogesh_pandey (10/6/2010)
Thank you for the reply. But the cur balance is in one table and the transaction is in another. I would have to use cur balance and remember the previous cur balance to calculate cur_bal for the previous transaction as I go up the list. I don't know if i make any sense. Please let me know.

the concept is still the same.; it's still a running total type of situation.

you join the main table to the transaction table to get the starting row and all the child rows. those two tables joined together are a query(just like the example data i posted);

then you can join them together, offset by a row_number() function.

without you providing the actual CREATE TABLE definitions and some sample data in a consumable format, like i posted, all i can do is offer concepts on how to tackle the issue.

or of course you could help us help you....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yogesh_pandey SSC Veteran Points: 204 More actions · Answer 4

Thank you. Can I send you a DDL and may be you can help. Please let me know.

Lowell SSC Guru Points: 323518 More actions · Answer 5

yogesh_pandey (10/6/2010)
Thank you. Can I send you a DDL and may be you can help. Please let me know.

post the DDL and sample data here; there's lots of volunteers on SSC that will jump to offer an example if you give them enough to work with in SSMS....

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

yogesh_pandey SSC Veteran Points: 204 More actions · Answer 6

Good Afternoon Lowell,

Here is the DDL. Please help. Thank you.

---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'

)