sqlmysqldateaccounting

Converting 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.


Solution

  • 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