Is there a way to use a windowing function to keep a minimum total of 0 instead of having to have a loop?

user_5758753 0
Viewed 43

I'm trying to avoid using a loop to have a running total. This is a simplified version; the real version includes subtotals by a couple of categories. I know I can do this using a windowing function [@Xata2], but I can't think of a non-loop way to do it if you restrict the total to non-negative (i.e., if the total is negative, use 0 instead) [@Xata3], because any conditions I put are on the current line, not the cumulative total.

    DECLARE   @Xata TABLE (
          ID            INTEGER IDENTITY
        , result        INTEGER
        )

    DECLARE   @Xata2 TABLE (
          ID            INTEGER IDENTITY
        , result        INTEGER
        , total         INTEGER
        )

    DECLARE   @Xata3 TABLE (
          ID            INTEGER IDENTITY
        , result        INTEGER
        , total         INTEGER
        )

    DECLARE   @result   INTEGER
            , @total    INTEGER
            , @Counter  INTEGER

    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
    INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)

    -- @Xata2: WINDOWING FUNCTION

    INSERT INTO @Xata2
        SELECT    result
                , SUM(result) OVER (ORDER BY ID)
            FROM @Xata

    SELECT    ID
            , result
            , total AS total_x2_neg
        FROM @Xata2

    SET @Counter = 0

    WHILE @Counter < (SELECT MAX(ID) FROM @Xata)
        BEGIN
            SET @Counter += 1

            SELECT @result = result FROM @Xata WHERE ID = @Counter
            SET @total = ISNULL((SELECT total FROM @Xata3 WHERE ID = @Counter - 1), 0)

            INSERT INTO @Xata3
                SELECT    @result
                        , IIF(@result + @total < 0, 0, @result + @total)

        END

    SELECT    ID
            , result
            , total AS total_x3_noneg
        FROM @Xata3

enter image description here

window-functions tsql sql-server
3 Answers

My first attempt was not returning correctly, thank you @Menno for the hint.

Try this approach with a recursive CTE

DECLARE   @Xata TABLE (ID INTEGER IDENTITY
                      ,result INTEGER);

INSERT INTO @Xata (result) VALUES(-3),(1),(2),(2),(0),(0),(-4),(-3),(2),(3);

WITH recCte AS
(
    SELECT ID
          ,result
          ,CASE WHEN result>0 THEN result ELSE 0 END AS runningTotalNoNeg 
    FROM @Xata 
    WHERE ID=1

    UNION ALL

    SELECT d.ID
          ,d.result
          ,CASE WHEN r.runningTotalNoNeg + d.result > 0 then r.runningTotalNoNeg + d.result ELSE 0 END
    FROM @Xata d
    INNER JOIN recCte r ON d.ID=r.ID+1
)
SELECT *
FROM recCte;

the result

ID  rt  runningTotalNoNeg
1  -3   0
2   1   1
3   2   3
4   2   5
5   0   5
6   0   5
7  -4   1
8  -3   0
9   2   2
10  3   5
user_50892040

EDIT

I wrapped the whole thing in another running total.

When

EXCELLENT repro by the way - I wish everyone worked like this

 DECLARE   @Xata TABLE (
      ID            INTEGER IDENTITY
    , result        INTEGER
    )



INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)
INSERT INTO @Xata (result) SELECT ROUND(RAND()* 9 - 4.5, 0)


SELECT x.ID, x.result, x.RT1, SUM(IIF(x.RT1<0,0,x.RT1)) OVER (ORDER BY ID) as RT2
from (
        SELECT  ID,  result
            , SUM(result) OVER (ORDER BY ID) as RT1
        FROM @Xata
    ) as x
ORDER BY x.ID
user_16901930

Try using sum window function with case statement:

select id, result,
       case when [sum] < 0 then 0 else [sum]
from (
    select id,
           result,
           sum(result) over (order by id) [sum]
    from MyTable
) a
user_71325500
Related