Generate Daily Balance From Transaction Table

Question

List item

I have this type of records:

...	user_id	...	ondate	...	current_balance	...
...	1	...	2021-06-30 05:21:03	...	20.95629595	...
...	1	...	2021-07-02 10:17:25	...	21.95629595	...
...	1	...	2021-07-02 11:07:23	...	20.95629595	...
...	14	...	2021-07-02 11:40:34	...	1.42046290	...
...	1	...	2021-07-02 11:59:15	...	21.87674098	...
...	1	...	2021-07-02 12:06:12	...	22.79712675	...
...	1	...	2021-07-02 12:24:11	...	21.79712675	...
...	14	...	2021-07-02 12:37:11	...	11.42046290	...
...

And I'm aiming to this result in MySQL:

user_id	date	average
1	2021-06-30	20.956295950000
1	2021-07-01	20.956295950000
1	2021-07-02	17.460644597143
1	2021-07-03	17.460644597143
1	2021-07-04	17.460644597143
1	2021-07-05	17.460644597143
... until the next date it has a different average value

If the next date after the average being calculated doesn't have any value, it will populate with previous average value.

This is my current query:

SELECT c.date,AVG(IFNULL(a.current_balance,0)) AS balance FROM users_balancemaster a 
RIGHT JOIN calendar c ON c.date= DATE(a.ondate) GROUP BY c.date

but it return zeros on dates where there's no average.

+---------+------------+-----------------+
| user_id | date       |     average     |
+---------+------------+-----------------+
|    1    | 2021-06-30 | 20.956295950000 |
|    1    | 2021-07-01 | 0.000000000000  | <<--
|    1    | 2021-07-02 | 17.460644597143 | 
|    1    | 2021-07-03 | 0.000000000000  | <<--
|    1    | 2021-07-04 | 0.000000000000  | <<--
|    1    | 2021-07-05 | 0.000000000000  | <<--
....

try a solution from here https://stackoverflow.com/questions/3538858/mysql-how-to-fill-missing-dates-in-range — nbk, Jul 16 '21 at 07:33
i understand that, but if you read the provided link there is exactly that described and solutions presented, in Mysql is no othger solution make a time table and left join it — nbk, Jul 16 '21 at 08:01
but link solution provide only '0' in missing date i want is above row current_balance — shubham, Jul 16 '21 at 08:56

score 1 · Accepted Answer · answered Jul 16 '21 at 09:46

the version you are using is not known to me, so i choose MysQL 5.7.

SELECT
`date`,
IF(balance IS NULL, @balance, @balance := balance) balance
FROM (select c.`date`
,AVG( a.current_balance)  as balance from users_balancemaster a 
RIGHT join calendar c on c.date= date(a.ondate) 
GROUP BY c.date
ORDER BY c.date) t1,(SELECT @balance := 0) t2

see example

Generate Daily Balance From Transaction Table

1 Answers1