Mysql - Recursively balance calculation

Question

I have following table for my financial system (php/mysql), with more than 5000 records. All balance fields are empty.

+---------+------+---------------------+---------+
| amount  | done | date                | balance |
+---------+------+---------------------+---------+
| 44.00   | 1    | 2018-02-13 14:23:32 | 0.00    |
+---------+------+---------------------+---------+
| 1000.00 | 0    | 2018-02-14 18:13:05 | 0.00    |
+---------+------+---------------------+---------+
| 500.00  | 1    | 2018-02-16 15:20:00 | 0.00    |
+---------+------+---------------------+---------+
| -40.00  | 1    | 2018-02-14 17:30:00 | 0.00    |
+---------+------+---------------------+---------+

When an user edit the field 'done' to 1, I need to update the balance field getting the amount for last payment done (based on date field).

+---------+------+---------------------+---------+
| amount  | done | date                | balance |
+---------+------+---------------------+---------+
| 44.00   | 1    | 2018-02-13 14:23:32 | 44.00   |
+---------+------+---------------------+---------+
| 1000.00 | 0    | 2018-02-14 18:13:05 | 0.00    |
+---------+------+---------------------+---------+
| 500.00  | 1    | 2018-02-16 15:20:00 | 544.00  |
+---------+------+---------------------+---------+
| -40.00  | 1    | 2018-02-14 17:30:00 | 4.00    |
+---------+------+---------------------+---------+

I can easily make a trigger to do it, but i cannot figure out how to update the balance for payments already done when the date of edited row is oldest (mysql don't allow run an update trigger in update context ).

Supposing that the user edit the second row, the balance of third, fourth and all following rows will updated too:

+---------+------+---------------------+---------+
| amount  | done | date                | balance |
+---------+------+---------------------+---------+
| 44.00   | 1    | 2018-02-13 14:23:32 | 44.00   |
+---------+------+---------------------+---------+
| 1000.00 | 1    | 2018-02-14 18:13:05 | 1004.00 |
+---------+------+---------------------+---------+
| 500.00  | 1    | 2018-02-16 15:20:00 | 1544.00 |
+---------+------+---------------------+---------+
| -40.00  | 1    | 2018-02-14 17:30:00 | 04.00   |
+---------+------+---------------------+---------+

How I can do it?

Technically, you can split the tables (have a seperate table for "done" plus primary key), you can then update that table using a trigger. Or you write to the table only with a procedure that runs two queries. Or you do not do it at all and calculate the balance on the fly when you query it (see e.g. [running total](https://stackoverflow.com/q/664700/6248528)). Or you find a different mechanism to represent "done" (I do not know what it means exactly, but you may e.g. have a table for invoices and one for payments and simply compare both sums). — Solarflare, Feb 17 '18 at 08:35

Rolland · Answer 1 · 2018-02-18T13:47:00.643

1

I solved my problem spliting this table to a new table (as @Solarflare suggested), maintening the original trigger in table 1 and runnig the following function in table 2:

DELIMITER $$
CREATE PROCEDURE `updateBalance`(IN `date_input` DATETIME)
NO SQL
BEGIN

  set @csum = 0;

  UPDATE transactions
  set balance = (@csum := @csum + amount)
  where date > date_input
  order by date asc;

END$$
DELIMITER ;

Based on: Create a Cumulative Sum Column in MySQL

edited Feb 18 '18 at 13:47

answered Feb 18 '18 at 13:16

Rolland

679
5
10

Good job, but you need to initialize `@csum` first (e.g. with `set @csum := 0` as the first line), like in the original post, otherwise you will get mainly random values. – Solarflare Feb 18 '18 at 13:43

Mysql - Recursively balance calculation

1 Answers1