Question 175397 in Mysql Question posted in
The official MySQL documentation can be found here.

How do I convert Ending Balance to Beginning Balance in MySQL for Accounting Data?

I’ve got a set of accounting data that I need to query using MySQL, but I’m having trouble figuring out how to convert the ending balance of a month into the beginning balance for the next month.

Here’s an example: In April 2020, the ending balance for account 101 is $200. I want this $200 to be the starting balance for May 2020 for the same account (101).

I’ve searched online for solutions, but they all assume you’re working with the current date, which doesn’t work for me. Can anyone help me out with this? I’d really appreciate it!

The table currently looks like this:

Date Account Name Balance
30 Appril 2020 101 Machine $200
30 May 2020 101 Machine $300
30 June 2020 101 Machine $400
30 July 2020 101 Machine $500

Expected Output:

Date Account Name Beginning Balance Ending Balance
30 Appril 2020 101 Machine null $200
30 May 2020 101 Machine $200 $300
30 June 2020 101 Machine $300 $400
30 July 2020 101 Machine $400 $500

Please note that the table is grossly oversimplified. There are hundreds of accounts, and there are years of transaction with multiple transactions per day.

As of now what I have done is just on the dashboard side, so quite tedious. If it is possible to do it through Sql, that would make it so much simpler.

Tags:

2

Answers


  1. 0

    Use INSERT INTO ... SELECT, where the SELECT query simply copies the April ending balance to May.

    INSERT INTO yourTable (date, account, name, beginning_balance, ending_balance)
SELECT '30 May 2020', account, name, ending_balance, NULL
FROM yourTable
WHERE date = '30 April 2020'
    Login or Signup to reply.
  2. 0

    As mentioned by @GMB in comments you can use the window function lag() to retrieve the row that comes before the current row :

    ALTER TABLE mytable ADD COLUMN beginning_Balance int;

UPDATE mytable t
INNER JOIN (
  select id, LAG(ending_Balance) OVER(PARTITION BY account ORDER BY date) as beginning_Balance
  FROM mytable
) as s ON s.id = t.id
SET t.beginning_Balance = s.beginning_Balance

    Demo here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search