By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (Name varchar, Total int, Payment int, BalanceForward int, Date date)
insert into t values
('A', NULL , NULL , 0 , null),
('A', 20 , 40 , NULL ,'01/01/2021'),
('A', 100 , 50 , NULL ,'02/01/2021'),
('A', 50 , 80 , NULL ,'03/01/2021'),
('B', NULL , NULL , 30 , null),
('B', 50 , 50 , NULL ,'01/01/2021'),
('B', 50 , 80 , NULL ,'02/01/2021'),
('B', 50 , 40 , NULL ,'03/01/2021'),
('C', NULL , NULL , 100 , null)
9 rows affected
select * from t
| Name | Total | Payment | BalanceForward | Date |
|---|---|---|---|---|
| A | null | null | 0 | null |
| A | 20 | 40 | null | 2021-01-01 |
| A | 100 | 50 | null | 2021-02-01 |
| A | 50 | 80 | null | 2021-03-01 |
| B | null | null | 30 | null |
| B | 50 | 50 | null | 2021-01-01 |
| B | 50 | 80 | null | 2021-02-01 |
| B | 50 | 40 | null | 2021-03-01 |
| C | null | null | 100 | null |
with bf as (
select * , Sum(total-payment) over(partition by name order by date) + sum(balanceforward) over(partition by name order by date) newbf
from t
)
update bf set balanceforward=newbf
where balanceforward is null
Warning: Null value is eliminated by an aggregate or other SET operation.
6 rows affected
select * from t
| Name | Total | Payment | BalanceForward | Date |
|---|---|---|---|---|
| A | null | null | 0 | null |
| A | 20 | 40 | -20 | 2021-01-01 |
| A | 100 | 50 | 30 | 2021-02-01 |
| A | 50 | 80 | 0 | 2021-03-01 |
| B | null | null | 30 | null |
| B | 50 | 50 | 30 | 2021-01-01 |
| B | 50 | 80 | 0 | 2021-02-01 |
| B | 50 | 40 | 10 | 2021-03-01 |
| C | null | null | 100 | null |