How best to approach a "bank account" application

[GaryS]

I want to set up a simple "bank account" application. Sounded like it would be the easiest thing in the world, but I'm struggling with how best to handle the running total, given that any of the debits/credits could be changed at any time. I wan tot be able to pull out transactions for a selected time period - and get the running total (without having to bring back all the records!)

Is there an "accepted way" of setting up this kind of thing?

[chazzy]

what do you mean...

SELECT * FROM TRANSACTIONS WHERE TRANSACTION_DATE BETWEEN START_DATE AND END_DATE;

SELECT SUM(TRANSACTION_AMOUNT) FROM TRANSACTIONS WHERE TRANSACTION_DATE BETWEEN START_DATE AND END_DATE;

Unless I'm just missing something...

[GaryS]

SUM(TRANSACTION_AMOUNT) will be key... but on its own it won't get me the "current balance"

[chazzy]

why won't it?

[GaryS]

Maybe it's me just being thick.

Say there are just two transactions in a selected period: 100 in and 50 out. I can see that the nett of the two transactions is +50... but what's the current balance? If balance at the beginning of the period was 1000, then the balance is now 1050. But I'm not seeing how I can get at that balance.

The only way I can think of is to maintain the balance separately, and adjust it every time a transaction is added or changed.

[chazzy]

typically banks, at midnight, have a job that runs to calculate "your balance as of date x". so that means your query would need to know the balance of date start_date and then the sum of the transactions between start and end dates. when i look at my bank account online, i see pending transactions that will probably be changed to finalized by end of day.

so yes, you should be using a job of some sorts to calculate their daily balance.