Finding cumulative sum using SQL Server with ORDER BY

Trying to calculate a cumulative sum up to a given number. Need to order by 2 columns : Delivery, Date.

Query:

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

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

Advertisement

Answer