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

Question

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

Answer 1

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