MySql Calculate Opening and Closing Balance

[wawane7256]

database table name tbl_log

ID  | month   | open_qty        | in_qty     | out_qty      | balance | item
----------------------------------------------------------------------------
1   | 2023-01 | 0               | 1          | 0            |  1      |  A
2   | 2023-02 | 1               | 2          | 1            |  2      |  A
3   | 2023-03 | 2               | 5          | 2            |  5      |  A
4   | 2023-04 | 5               | 0          | 1            |  4      |  A

I am not able to create an idea for calculating an opening balance for each month where item = A
I am trying to achieve by using but no idea, I am using MySql query please help to calculate opening balance of the each month
please help

SELECT
    COALESCE(SUM(QTY),
    0) AS `OpeningBalance`
FROM
    (
    SELECT
        `in_qty` + `open_qty` QTY
    FROM
        `tbl_log` CR
    WHERE
        CR.item = 'A'
) t

[benanamen]

Your DB structure is not right for doing this. Deposits and withdrawals should be a single column. Aside from using a transaction_type column to determine a deposit/withdrawl you could use positive/negative numbers instead. There are pros and cons to doing it that way.

Here is a quick example for Mysql 8.

-- Creating the table
CREATE TABLE account_transactions (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    transaction_date DATE,
    transaction_type ENUM('deposit', 'withdrawal'),
    amount DECIMAL(10, 2)
);

-- Inserting sample data
INSERT INTO account_transactions (transaction_date, transaction_type, amount) VALUES
('2023-01-01', 'deposit', 1000.00),
('2023-02-03', 'withdrawal', 200.00),
('2023-03-05', 'deposit', 500.00),
('2023-04-07', 'withdrawal', 150.00),
('2023-05-09', 'deposit', 750.00),
('2023-06-11', 'withdrawal', 300.00),
('2023-07-13', 'deposit', 1200.00),
('2023-08-15', 'withdrawal', 500.00),
('2023-09-17', 'deposit', 450.00),
('2023-10-19', 'withdrawal', 250.00);

SHOW RUNNING BALANCE REGISTER

SELECT
    ID,
    transaction_date,
    transaction_type,
    amount,
    SUM(CASE WHEN transaction_type = 'deposit' THEN amount ELSE -amount END) 
        OVER (ORDER BY transaction_date, ID) AS running_balance
FROM
    account_transactions
ORDER BY
    transaction_date, ID;

SHOW OPENING BALANCE BY MONTH

SELECT
    DATE_FORMAT(sub.first_of_month, '%Y-%m') AS month,
    COALESCE((
        SELECT SUM(CASE WHEN at.transaction_type = 'deposit' THEN at.amount ELSE -at.amount END)
        FROM account_transactions at
        WHERE at.transaction_date < sub.first_of_month
    ), 0) AS opening_balance
FROM
    (SELECT DISTINCT
        LAST_DAY(transaction_date - INTERVAL 1 MONTH) + INTERVAL 1 DAY AS first_of_month
     FROM 
        account_transactions) sub
ORDER BY
    sub.first_of_month;

[m_hutley]

I agree that if you’re building a ledger, the style that benanamen has provided is the way to go.

That said.

opening balance… okay… the data will be redundant.

UPDATE tbl_log main
INNER JOIN tbl_log preva 
ON (preva.month - INTERVAL -1 MONTH) = main.month
SET main.open_qty = preva.balance
WHERE main.month > "2023-01-01";

NOTE: I have converted month to an actual DATE column to make this work.

DB Fiddle - SQL Database Playground

[m_hutley]

(You can of course just SELECT this information, if you’re just trying to pull it, as a simple SELECT…LEFT JOIN)