Union-all get previous column data in next column MySQL PHP

General PHP Help
#1

I have an issue while calculating the column-like for bank statements. actually, I am trying to calculate Opening Balance and Closing Bal in MySQL PHP.
I have two tables. material_in for IN + entry and material_approvefor OUT -.
Everything is perfect in my code but I am not able to calculate the opening and the closing balance.
I am trying to display the previous closing balance will show the next column as the opening balance. and for first time the opening balance will by default 0.

I am new in PHP and I am here using Union All function. Please help me to create the function for getting the Opening / and Closing calculation in my code.

I used the CASE WHEN function but I could not able to implement the previous column data to the next row column.

Please help to achieve the desired output in mu code.

table- material_in

id components_key insert_date credit transaction_type
1 12345 2021-04-16 100 IN
2 12345 2021-04-16 50 IN

table- material_approve

id components_key insert_date debit transaction_type
1 12345 2021-04-16 30 OUT
2 12345 2021-04-16 2 OUT

I am trying the code -

SELECT
    `u`.`component_key`,
    `u`.`insert_date`,
    `u`.`transaction_qty`,
    `u`.`transaction_type`,
    CASE WHEN `u`.`query_type` = 'IN' THEN `u`.`transaction_qty` ELSE '0' END AS `today_credit`,
    CASE WHEN `u`.`query_type` = 'OUT' THEN `u`.`transaction_qty` ELSE '0' END AS `today_debit`,	
    CASE WHEN `u`.`query_type` = 'IN' OR `u`.`query_type` = 'OUT' THEN (`close_bal`) ELSE '0' END AS `open_bal`,
    CASE WHEN `u`.`query_type` = 'IN' OR `u`.`query_type` = 'OUT' THEN (`open_bal` + `today_credit`-`today_debit`) ELSE '0' END AS `close_bal`,
FROM
    (
    SELECT
        `components_key` AS `component_key`,
        `insert_date`,
        `credit` AS `transaction_qty`,
        CASE WHEN `transaction_type` = 'OA' THEN 'IN' WHEN `transaction_type` = 'O' THEN 'IN' WHEN `transaction_type` = 'I' THEN 'IN' ELSE '--'
        END AS `query_type`, null AS today_debit, null AS today_credit, null AS open_bal, null AS close_bal
    FROM
        `material_in`
    UNION ALL
    SELECT
        `components_key`,
        `insert_date`,
        `debit` AS `transaction_qty`,
        CASE WHEN `transaction_type` = 'OA' THEN 'OUT' WHEN `transaction_type` = 'I' THEN 'OUT' WHEN `transaction_type` = 'O' THEN 'OUT' ELSE '--'
        END AS `query_type`, null AS today_debit, null AS today_credit, null AS open_bal, null AS close_bal
    FROM
        `material_approve`
    ) u
WHERE
    `u`.`component_key` = '12345'
ORDER BY
    `u`.`insert_date`
DESC

the output comes -

components_key insert_date transaction_qty transaction_type today_credit today_debit open_bal close_bal
12345 2021-04-16 100 IN 100 0 NULL NULL
12345 2021-04-16 50 IN 50 0 NULL NULL
12345 2021-04-16 30 OUT 0 30 NULL NULL
12345 2021-04-16 2 OUT 0 2 NULL NULL

but I want it comes like this below
the output comes -

output will be-

components_key insert_date transaction_qty transaction_type today_credit today_debit open_bal close_bal
12345 2021-04-16 100 IN 100 0 0 100
12345 2021-04-16 50 IN 50 0 150 200
12345 2021-04-16 30 OUT 0 30 200 170
12345 2021-04-16 2 OUT 0 2 170 168

FORMULA FOR CLOSE BAL = open_bal + today_credit - today_debit = close_bal

FORMULA FOR OPEN BAL = close_bal + today_credit - today_debit = open_bal

#2

This isn’t PHP it’s MySQL. So, why not just sum both. If you just want balances, you can use SUM.
Like SELECT SUM(credit) or SUM(debit) for each table. And, for totals, something loosely like this:
SELECT ( SUM(material_in.credit) - SUM(material_approve.debit) ) AS balance
This is just an example. You can total debits and credits and calculate the current balance. Let the SQL system work for you.

#3

I want to show it as a bank statement.
I have been made the function for getting today’s balance and today’s out the balance. and I have been updated on the question. please help me in my code how I show the previous closing col in the next open col.

#4

Well, in my humble opinion, when you insert data into your tables, you should update the balances.
Not when you display the details. But, if you want to do this line by line, you have two choices.
Either add the opening and closing fields to the tables and calculate them each time data is inserted,
Or, calculate them as you display the data. Since, I am guessing you want to do this the second way,
you would just have to create the totals along the display code. You must have a while() function where you display your data. Just before that set the $opening_balance=0; and $closing_balanced=0;
Then, as you display each line, adjust those totals and display them. Should be easy.
But, you did not show your display code, so I can not tell you where to put the code in.
Hope that helps…

Sponsor our Newsletter | Privacy Policy | Terms of Service