Si votre ID
est unique pour chaque ligne, puis retirez le PARTITION BY ID
sinon, il ne somme cumulative. Pour inverser l'ordre de fonctionnement total, il vous suffit d'inverser le ORDER BY
de la SUM
:
DDL:
declare @sales table (
ID int,
Date date,
Amount int);
insert into @sales
values
(1, '2020-01-01', 15),
(2, '2020-01-02', 10),
(3, '2020-01-03', 5);
DML:
select ID, Date, Amount,
sum(Amount) over (order by Date) as RunningBalanceForward,
sum(Amount) over (order by Date desc) as RunningBalanceBackwards
from @sales
order by ID
Résultats:
ID |
Date |
Montant |
RunningBalanceForward |
RunningBalanceBackwards |
1 |
2020-01-01 |
15 |
15 |
30 |
2 |
2020-01-02 |
10 |
25 |
15 |
3 |
2020-01-03 |
5 |
30 |
5 |