# How to calculate running balance for many accounts?

What I need to do is:

• Calculate running balance (debit- credit) by account_no
• Reset the balance when account_no changes
• Reset the balance if transaction_date is in a new financial period AND account_type > 5

Query

``````SELECT
acc_account_transactions.id,
account_no,
account_type,
transaction_id,
DATE_FORMAT(acc_transactions.transaction_date,
'%d/%m/%Y') transaction_date,
debit_balance,
credit_balance,
IFNULL(acc_finacial_periods.id, - 1),
(@current_period_id:=IF(IFNULL(acc_finacial_periods.id, - 1) <> @current_period_id,
IFNULL(acc_finacial_periods.id, - 1),
@current_period_id)) current_period_id,
(@running_balance:=IF(@curr_account_no < account_no
OR (@current_period_id <> IFNULL(acc_finacial_periods.id, - 1)
AND account_type > 5),
0,
@running_balance)) prev_runnng_bal,
(@curr_account_no:=IF(@curr_account_no < account_no,
account_no,
@curr_account_no)) curr_account_no,
(@running_balance:=@running_balance + (acc_account_transactions.debit_balance - acc_account_transactions.credit_balance)) AS running_balance
FROM
(SELECT
@running_balance:=0,
@curr_account_no:=0,
@current_period_id:=- 1
) r,
acc_account_transactions
INNER JOIN
acc_transactions ON acc_transactions.id = acc_account_transactions.transaction_id
INNER JOIN
acc_accounts ON acc_accounts.id = acc_account_transactions.account_no
LEFT JOIN
acc_finacial_periods ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
ORDER BY account_no , acc_account_transactions.id
``````

What's wrong with it now?

• current_account_no variable has a wrong value, -it should be same as account_no column- hence, the running_balance result is also incorrect

Fiddle: here

Note: Database server info is below

Please don't close my question, I really need help :/

EDIT

current wrong result as in the fiddle (last query):

what I expect, something like this:

• with so complicated system you also need to show what result you expect from your query. still your logic seems somewhat off, because as you use the same user defined variable twice to set account.
– nbk
Commented Jun 13, 2020 at 13:40
• @nbk, I edited the question with images showing the problem Commented Jun 13, 2020 at 13:52

There is something niot right, but you can take it from here.

I replaced your FROM clause, because you need a sorted list to do that coalculation and mariadb takes the definition, that tables are unsorted literary and eliminates all sorting in subqueries. so you have to circumvent it

Check the numbers, i think you must work on that.

``````SELECT  id,account_no,account_type,transaction_id,
DATE_FORMAT(transaction_date,
'%d/%m/%Y') transaction_date,
debit_balance,
credit_balance,
IFNULL(afp_id, - 1),
(@current_period_id:=IF(IFNULL(afp_id, - 1) <> @current_period_id,
IFNULL(afp_id, - 1),
@current_period_id)) current_period_id,
(@running_balance:=IF(@curr_account_no < account_no
OR (@current_period_id <> IFNULL(afp_id, - 1)
AND account_type > 5),
0,
@running_balance)) prev_runnng_bal
,
(@curr_account_no:=IF(@curr_account_no < account_no,
account_no,
@curr_account_no)) curr_account_no
,

(@running_balance:=@running_balance
+ (debit_balance - credit_balance)) AS running_balance
FROM

(SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
,afp.id as afp_id FROM
acc_account_transactions aat
INNER JOIN
acc_transactions at ON at.id = aat.transaction_id
INNER JOIN
acc_accounts aac ON aac.id = aat.account_no
LEFT JOIN
acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
ORDER BY account_no,aat.id LIMIT 18446744073709551615 ) aat1
,(SELECT
@running_balance:=0,
@curr_account_no:=0,
@current_period_id:=- 1
) r
``````

db<>fiddle here

To yxpand the idea

``````SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
,afp.id as afp_id FROM
acc_account_transactions aat
INNER JOIN
acc_transactions at ON at.id = aat.transaction_id
INNER JOIN
acc_accounts aac ON aac.id = aat.account_no
LEFT JOIN
acc_finacial_periods afp ON DATE_FORMAT(transaction_date, '%Y/%m/%d') >= DATE_FORMAT(startdate, '%Y/%m/%d')
AND DATE_FORMAT(transaction_date, '%Y/%m/%d') <= DATE_FORMAT(enddate, '%Y/%m/%d')
ORDER BY account_no,aat.id LIMIT 18446744073709551615
``````

Builds the basis for the calculation.

It is a new sub query that sorts the data given so that you can get the wanted result.

the `LIMIT 18446744073709551615` is there,because when mariadb processes the query above, it would remove the order by that is needed.

``````SELECT aat.*,at.debit, at.credit, at.transaction_date, aac.account_type
,afp.id as afp_id
``````

are the columns that are needed for the calculation, if you need more column you first have to include them here. be aware mysql/mariadb doesn't like the same column name, so i have to `afp.id as afp_id` give the secnd id fiekld an alias.

• your edit seems to be working fine, I'm testing cases I have now. Can you please explain your edits? Commented Jun 13, 2020 at 16:47
• i explained what i changed. take a look at the from clause and read my text to that topic. if something is till unlcear please ask, (hint ORDER BY account_no,aat.id LIMIT 18446744073709551615 ) aat1 and also use aliases please you see it is much more comprehensive
– nbk
Commented Jun 13, 2020 at 16:52
• I mean I don't understand clearly the issue you mentioned "mariadb takes the definition, that tables are unsorted literary" Commented Jun 13, 2020 at 17:07
• In your original query the data are not sorted, that why you get the false result. so you need to sort them before the algorithm starts. i b mysql You sort acc_account_transactions in a subquery and the rest joined is also sorted. in mariadb, you see in the quiery you have to join all tables and sort it, before you let your algorithm run
– nbk
Commented Jun 13, 2020 at 17:13