Is it possible to do a running balance?

Michael_Morris1 · December 16, 2010, 9:10pm

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…


      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 · December 16, 2010, 9:14pm

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_Morris1 · December 16, 2010, 9:21pm

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.

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 · December 16, 2010, 9:32pm

Cool cool. Glad you found your answer!

Michael_Morris1 · December 16, 2010, 9:45pm

Not sure I have. Just reading.

Michael_Morris1 · December 17, 2010, 12:35am

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


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 &gt;= :startDate
		AND t.fiscal_date &lt;= :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…


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 · December 17, 2010, 1:09am

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

what database system is it?

Michael_Morris1 · December 17, 2010, 1:23am

mySQL

I have something that is working


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 &gt;= :startDate
		AND t.fiscal_date &lt;= :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 &gt;= :startDate
		AND t.fiscal_date &lt;= :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 · December 17, 2010, 3:45am

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 –

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 –

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_Morris1 · December 17, 2010, 2:52pm

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 · December 17, 2010, 3:01pm

check out this article – http://articles.sitepoint.com/article/simply-sql-the-from-clause

Michael_Morris1 · December 21, 2010, 8:35pm

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 · December 21, 2010, 8:53pm

range? what range? which query?

Michael_Morris1 · December 21, 2010, 10:04pm

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_Morris1 · December 22, 2010, 5:43pm

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 · December 22, 2010, 5:52pm

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…