Sql-server – Set non-negative floor for rolling sum, in SQL Server

running-totalssql serversql-server-2008

I need to set a floor on the rolling sum calculation. For example, with

PKID    NumValue    GroupID
----------------------------
1       -1          1
2       -2          1
3       5           1
4       -7          1
5       1           2

I would like to have:

PKID   RollingSum    GroupID
----------------------------- ## Explanation:
1      0             1        ## 0 - 1 < 0  => 0
2      0             1        ## 0 - 2 < 0  => 0
3      5             1        ## 0 + 5 > 0  => 5
4      0             1        ## 5 - 7 < 0  => 0

When adding a negative number will cause the sum to be negative, the limit will be activated to set the result as zero. Subsequent addition should be based on this adjusted value, instead of the original rolling sum.

The expected result should be achieved using addition. If the fourth number changes from -7 to -3, the fourth result should be 2 instead of 0

If a single sum can be provided rather than a few rolling numbers, it would also be acceptable. I can use stored procedures to implement a non-negative addition, but that would be too low-level.

The real life problem for this is that we record order placed as positive amount and cancelled as negative. Due to connectivity issues customers may click the cancel button more than once, which will result in multiple negative values being recorded. When calculating our revenue, "zero" need to be a boundary for sales.

This business application is absolutely stupid, but there's nothing I can do about that. For this question, please only consider solution that can be used by a DBA.

I expect fifty rows per GroupID at most.

Here's a recursive CTE example I came up with (which seems to work). Is uses Row_Number() OVER to create a sequence number with no gaps. I don't know how well it would perform with your data, but it's something to try.

--Set up demo data
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
drop table #temp
go
create table #temp (PKID int, NumValue int, GroupID int)
insert into #temp values
(1,-1,1), (3,-2,1), (5,5,1), (7,-3,1), (9,1,2)

--here is the real code
;
(
SELECT
ROW_NUMBER() OVER(ORDER BY PKID ASC) AS rn,
* from #temp
)
,x
AS (
SELECT PKID         --Anchor row
,NumValue
,RunningTotal = CASE
WHEN NumValue < 0   --if initial value less than zero, make zero
THEN 0
ELSE NumValue
END
,GroupID
,rn
WHERE rn = 1

UNION ALL

SELECT y.PKID
,y.NumValue
,CASE
WHEN x.GroupID <> y.groupid     --did GroupId change?
THEN CASE
WHEN y.NumValue < 0     --if value is less than zero, make zero
THEN 0
ELSE y.numvalue         --start new groupid totals
END
WHEN x.RunningTotal + y.NumValue < 0    --If adding the current row makes the total < 0, make zero
THEN 0
ELSE x.RunningTotal + y.NumValue        --Add to the running total for the current groupid
END
,y.Groupid
,y.rn
FROM x
INNER JOIN RowNumberAddedToTemp AS y ON y.rn = x.rn + 1
)
SELECT PKID
,Numvalue
,RunningTotal
,GroupID
FROM x
ORDER BY PKID
OPTION (MAXRECURSION 10000);