SQL statement running balance query with previous balance taken into account

Question

Hi Guys

I have a SQL statement which caclulates the running balance
for a list of transactions in a transactions table. This SQL
statement is as follows:

SELECT transID, debit, credit,
(SELECT SUM(debit-credit) 
FROM transactions as D1 
WHERE D1.transID <= D0.transID) AS balance
FROM transactions AS D0

The only problem I'm having is that I have to display
transactions between a particular date range. I have for eg.
transID, transDate, debit, credit fields in my db.

However the problem I'm having is that when the transaction
records are pulled out for the specified date range the balances
are only calculated for those records. I need someway of having a
balance b/f (brought forward) so that the selected records use that
as a 'starting' balance and then calculate the running balance as
normal.

Any easy solutions?

Many, many thanks for your help in advance.

All the best

Wesley

Fetch · Answer

You'd need to determine the b/f then apply it against the transaction records when they are pulled for a date range. So if the date range is

Feb 1st, 2009 -> Feb 28th, 2009

You'd need to also get the previous balance in a separate query, then use it to correctly display the running balance when you output the date range.

I suppose a simpler way involving a little more DB work (and possibly lots of coding) is to store the current balance as well with each transaction; then when performing a date range you'd have the balance as well and not have to calculate it on the fly.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded