How to calculate running balance for many accounts?

Question

please help me fix query below to calculate running balance correctly.

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:

Answer 1

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.