Is it possible to do a running balance?

**Michael Morris** · Dec 16, 2010 15:10

I'm currently doing a report that does a T ledger. It needs to do a running balance from a known start point. I'm trying to migrate the logic for this into SQL as much as possible and querying a running balances is something I haven't figured out...

Code:

      Credit  Debit  Balance 
start     --     --   100.00
row1   10.00          110.00
row2           20.00   90.00
row3   10.00          100.00

As can be seen, the balance of each row id dependent on the contents of the row that preceded it. This information is NOT precalculated at insert time nor can it be since the ledger needs to reflect changes brought by query filters that determine which rows show up. So, can this be done in SQL alone?

**svcghost** · Dec 16, 2010 15:14

So would it not be possible to, upon insert, create a new record that has a Balance value of Balance + Credit or Balance - Debit depending on what was inputted? And then just also input the Debit or Credit value too. How are you getting this data? A textbox in a form??

**Michael Morris** · Dec 16, 2010 15:21

Data is out of Mysql - that text box is to show what I mean by a running balance. After making the first post I hit this in Google.

http://www.databasejournal.com/featu...t-a-Cursor.htm

Reading over that now, but the thread is still useful cause I'm sure this comes up from time to time.

And writing the value at insert time is not possible, because we want the balances to change depending on search criteria. In the quick example I gave, if the user decided to reverse the order the rows are displayed the balances need to be recalculated to follow their new order on the page. This is strictly derived data.

**svcghost** · Dec 16, 2010 15:32

Cool cool. Glad you found your answer!

**Michael Morris** · Dec 16, 2010 15:45

Not sure I have. Just reading.

**Michael Morris** · Dec 16, 2010 18:35

Making progress, but could use some help. This is what I have so far.

Code sql:

SET @itemBalance = 0;
 
SELECT
	worksheet.accountid AS id, 
	worksheet.`type` AS `type`, 
	worksheet.`number` AS `number`, 
	worksheet.`name` AS `name`,
 
	worksheet.itemid AS items_id,
	worksheet.debit AS items_debit,
	worksheet.credit AS items_credit,
	(@itemBalance := @itemBalance + worksheet.amount) AS items_balance,
	worksheet.fiscaldate AS items_fiscaldate,
 
	worksheet.description AS items_description,
	worksheet.receiptid AS items_receiptid,
	worksheet.transactionid AS items_transactionid
FROM (
	SELECT 
		a.`id` AS accountid, 
		`type` AS `type`, 
		`full_account_number` AS `number`, 
		`name` AS `name`,
		i.id AS itemid,
		i.debit AS debit,
		i.credit AS credit,
		DATE_FORMAT(t.fiscal_date, '%c/%e/%Y') AS fiscaldate,
		t.description AS description,
		r.receipt_id AS receiptid,
		t.id AS transactionid,
 
		IF ( a.`type` IN ('REVENUE', 'ASSET', 'EQUITY'),
			i.credit - i.debit,
			i.debit - i.credit
		) AS 'amount'
	FROM gl_transaction_items i 
		LEFT JOIN gl_accounts a ON i.glaccount_id = a.id
		LEFT JOIN gl_transactions t ON t.id = i.gltransaction_id
		LEFT OUTER JOIN gl_auto_post_history r ON r.transaction_id = t.id	
	WHERE a.id = :account
		AND t.fiscal_date >= :startDate
		AND t.fiscal_date <= :endDate 
		AND (t.voided = 0 OR t.voided IS NULL )
		AND t.posted = 1
	ORDER BY t.fiscal_date DESC 
) AS worksheet

What I'm doing is running a query to get my information, then selecting from that query. As I pull that information in the @itemBalance variable is able to create a running total for my ledger. The output looks like this..

Code:

Credit 	Debit   	Balance
$ 66.67 	$ 0.00 	$ 66.67
$ 17.47 	$ 0.00 	$ 84.14
$ 12.83 	$ 0.00 	$ 96.97
$ 332.97 	$ 0.00 	$ 429.94

Now comes the next part that I'm having trouble with. I need totals for the credit and debit column - and the final balance is the difference of the two. The entry of the final balance MUST match the balance displayed on the last column for the report to be valid. Yeah, I don't expect computers to fail at math - but I could fail in coding

Anyway, normally I'd do a group by clause followed up with a sum() call - but I don't want to disturb the ability of the query to pull the individual amounts.

**r937** · Dec 16, 2010 19:09

Originally Posted by Michael Morris

So, can this be done in SQL alone?

yes, it can, but it's often quite inefficient, and better done in the front end

what database system is it?

**Michael Morris** · Dec 16, 2010 19:23

mySQL

I have something that is working

Code sql:

SELECT
	worksheet.accountid AS id, 
	worksheet.`type` AS `type`, 
	worksheet.`number` AS `number`, 
	worksheet.`name` AS `name`,
	totals.credit AS totalcredit,
	totals.debit AS totaldebit,
	worksheet.itemid AS items_id,
	worksheet.debit AS items_debit,
	worksheet.credit AS items_credit,
	(@itemBalance := @itemBalance + worksheet.amount) AS items_balance,
	worksheet.fiscaldate AS items_fiscaldate,
 
	worksheet.description AS items_description,
	worksheet.receiptid AS items_receiptid,
	worksheet.transactionid AS items_transactionid
FROM (
	SELECT 
		a.`id` AS accountid, 
		`type` AS `type`, 
		`full_account_number` AS `number`, 
		`name` AS `name`,
		i.id AS itemid,
		i.debit AS debit,
		i.credit AS credit,
		DATE_FORMAT(t.fiscal_date, '%c/%e/%Y') AS fiscaldate,
		t.description AS description,
		r.receipt_id AS receiptid,
		t.id AS transactionid,
 
		IF ( a.`type` IN ('REVENUE', 'ASSET', 'EQUITY'),
			i.credit - i.debit,
			i.debit - i.credit
		) AS 'amount'
	FROM gl_transaction_items i 
		LEFT JOIN gl_accounts a ON i.glaccount_id = a.id
		LEFT JOIN gl_transactions t ON t.id = i.gltransaction_id
		LEFT OUTER JOIN gl_auto_post_history r ON r.transaction_id = t.id	
	WHERE a.id = :account
		AND t.fiscal_date >= :startDate
		AND t.fiscal_date <= :endDate 
		AND (t.voided = 0 OR t.voided IS NULL )
		AND t.posted = 1
	ORDER BY t.fiscal_date DESC 
) AS worksheet
JOIN (
	SELECT
		glaccount_id AS accountid,
		SUM(i.credit) AS credit,
		SUM(i.debit) AS debit
	FROM gl_transaction_items i 
		LEFT JOIN gl_transactions t ON t.id = i.gltransaction_id
	WHERE i.glaccount_id = :account
		AND t.fiscal_date >= :startDate
		AND t.fiscal_date <= :endDate 
		AND (t.voided = 0 OR t.voided IS NULL )
		AND t.posted = 1
	GROUP BY i.glaccount_id
) AS totals ON totals.accountid = worksheet.accountid

Not sure if its the most efficient setup, but it is working.

**r937** · Dec 16, 2010 21:45

Originally Posted by Michael Morris

mySQL

thanks, i have flagged the thread to have it moved to the mysql forum

if what you have works, i'd go with it

did you have a concern?

i noticed something which could be improved, if only slightly...

when you do this --

Code:

FROM gl_transaction_items i 
LEFT JOIN gl_accounts a ON i.glaccount_id = a.id
LEFT JOIN gl_transactions t ON t.id = i.gltransaction_id
LEFT OUTER JOIN gl_auto_post_history r ON r.transaction_id = t.id

you are allowing for the possibility that a transaction item can have an account_id that isn't in the accounts table, or a transaction_id that isn't in the transactions table, or a transaction can have an id that isn't in the auto_post_history table

if those cases must be allowed for, fine, otherwise i suspect the joins should be INNER JOINs instead

also when you do this --

Code:

WHERE a.id = :account
AND t.fiscal_date >= :startDate
AND t.fiscal_date <= :endDate 
AND (t.voided = 0 OR t.voided IS NULL )
AND t.posted = 1

it automatically turns two of those left outer joins into behaving exactly like inner joins anyway, so you might as well write them that way, as that can often improve performance

**Michael Morris** · Dec 17, 2010 08:52

Still not clear on the difference between inner and outer joining, but I'll switch that up and see if it helps.

That auto_post_history table has a weird name (I didn't name it) -- it exists to join the transaction and receipt table and only has 3 columns - transaction, receipt and its own primary key. Normally when it's joined the query joins the receipt table as well, but in this particular circumstance I don't need any data off the receipt table - just the id which I can get with that cross reference table.

**r937** · Dec 17, 2010 09:01

Originally Posted by Michael Morris

Still not clear on the difference between inner and outer joining

check out this article -- http://articles.sitepoint.com/articl...he-from-clause

**Michael Morris** · Dec 21, 2010 14:35

New problem - how do I modify the above to work over a range? That is, I now need the query to pull multiple accounts. Each account has it's own total independent of the others, but the query I'm using is doing a running total over all rows as they are pulled

**r937** · Dec 21, 2010 14:53

range? what range? which query?

**Michael Morris** · Dec 21, 2010 16:04

The query I settled on above works fine for 1 account. Now if I want to pull more than 1 account at a time.... boom.

The sql var doesn't reinit between different accounts.

I've given up trying to do it in SQL and am going to add it up in PHP. If anyone wants to see if they can come up with a solution I'm curious, but I gots to get something out the door.

**Michael Morris** · Dec 22, 2010 11:43

I didn't lose enough time by doing it PHP side to justify trying to redo the code with SQL calculation - though I am curious enough to maybe do it on my own time. The PHP version pulls 2 years worth of data in under 3 minutes, which is more than fast enough for this function (only one or two users would ever run the report in question).

**r937** · Dec 22, 2010 11:52

if the SQL currently takes 3 minutes to return the data wihout a running total, then i have very little hope that adding a running total to the SQL will let the query finish in anywhere near acceptable times...

User Tag List

Thread: Is it possible to do a running balance?

Thread Tools

Display

Is it possible to do a running balance?

Bookmarks

Bookmarks

Posting Permissions