Running Balance

Question

Running Balance

noblepaulaziz

Grasshopper

Points: 15
More actions
May 21, 2012 at 8:08 pm

#149329

Hi Folks
I have a double entry transaction table named trans which accumulates both debit and credit transactions. The structure of the table is shown below.
[accountcredited]
[accountdbited]
[amount]
[dateoccurred]
The trans table is linked to accounts table whose structure is shown below
[accountno]
[accountname]
[balance]; i.e the current balance each account.
I am able to calculate the current balance for each account.
My problem is how generate running balances for each account like bank transaction showing the transactions that gave each account its balance to date.
I cannot figure out how to handle this problem.
Any assistance would greatly appreciated.

Viewing 5 posts - 1 through 5 (of 5 total)

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

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 1

Piece-o-cake. The only thing is that the method requires some fairly stringent rules. It's worth it because it'll do the required running total on a million rows in just about 2 seconds on most machines.

Because there are some stringent rules for doing this at these speeds, I need to test using your columns on your table. I also need you to also post some test data that you can verify the code as working correct with. Please see the first link in my signature line below for how to do both in a format that would help us both the most. Thanks.

Last but not least, I need you to post any indexes you may have on the table as well as what the Primary Key is.

And, yeah... it really will be worth it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noblepaulaziz Grasshopper Points: 15 More actions · Answer 2

AccountNo is a primary key in the Accounts table. Both acctdebited and acctcredited are foreign keys.

My test data is shown below.

acctdebited acctcredited

noblepaulaziz Grasshopper Points: 15 More actions · Answer 3

Please discard the earlier reply, it was accidentally done

AccountNo is a primary key in the Accounts table. Both acctdebited and acctcredited are foreign keys.

My test data is shown below.

[acctdebited] [acctcredited] [ amount]

[4089] [4081] [78.00]

[4090] [1000] [100.00]

[1045] [4140] [310.00]

[2099] [3089] [50.00]

[3000] [8090] [120.00]

Thank you

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 4

noblepaulaziz (5/22/2012)
Please discard the earlier reply, it was accidentally done
AccountNo is a primary key in the Accounts table. Both acctdebited and acctcredited are foreign keys.
My test data is shown below.
[acctdebited] [acctcredited] [ amount]
[4089] [4081] [78.00]
[4090] [1000] [100.00]
[1045] [4140] [310.00]
[2099] [3089] [50.00]
[3000] [8090] [120.00]
Thank you

Repeating my earlier message... Please see the first link in my signature line below for how to post data. What you posted doesn't help much and I'm certainly not going to take the time to convert your data to a working set of INSERTs. Help me help you and read the article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)