Trying to calculate a cumulative sum up to a given number. Need to order by 2 columns : Delivery, Date.
Query:
x
SELECT Date, Delivery, Balance, SUM(Balance) OVER ( ORDER BY Delivery, Date) AS cumsum
FROM t
Results:
Contract_Date Delivery Balance cumsum
2020-02-25 2020-03-01 308.100000 308.100000
2020-03-05 2020-03-01 -2.740000 305.360000
2020-03-06 2020-04-01 176.820000 682.180000
2020-03-06 2020-04-01 200.000000 682.180000
2020-03-09 2020-04-01 300.000000 1082.180000
2020-03-09 2020-04-01 100.000000 1082.180000
2020-03-13 2020-04-01 129.290000 1211.470000
2020-03-16 2020-04-01 200.000000 1711.470000
2020-03-16 2020-04-01 300.000000 1711.470000
2020-03-17 2020-04-01 300.000000 2011.470000
2020-04-01 2020-04-01 86.600000 2098.070000
2020-04-03 2020-04-01 200.000000 2298.070000
Expected results:
Contract_Date Delivery Balance cumsum
25/2/2020 1/3/2020 308.1 308.1
5/3/2020 1/3/2020 -2.74 305.36
6/3/2020 1/4/2020 176.82 482.18
6/3/2020 1/4/2020 200 682.18
9/3/2020 1/4/2020 300 982.18
9/3/2020 1/4/2020 100 1082.18
13/3/2020 1/4/2020 129.29 1211.47
16/3/2020 1/4/2020 200 1411.47
16/3/2020 1/4/2020 300 1711.47
17/3/2020 1/4/2020 300 2011.47
1/4/2020 1/4/2020 86.6 2098.07
3/4/2020 1/4/2020 200 2298.07
Version:
Microsoft SQL Server 2017
Advertisement
Answer
You need a third column in the ORDER BY
clause to break the ties on Contract_Date
and Delivery
. It is not obvious which one you would use. Here is one option using column Balance
:
SELECT
Date,
Delivery,
Balance,
SUM(Balance) OVER ( ORDER BY Delivery, Contract_Date, Balance) AS cumsum
FROM t