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