MSSQL distribute negative amount to positive amount rows

Question

i think this is possible, but i stuck in the cumulative sums. I have a positive amounts and a single negative amount joined at the top. Any possible way to distribute this negative amount to collapse positive amount table?

ROW_NUM AMOUNT N_AMOUNT NEEDED_RESULT
------- ------ -------- -------------
1       100.00 NULL     100
2       300.00 NULL     200
3       300.00 -400.00  0

table example

@Alan it's one of the decisions but with OVER ... UNBOUND will looks better. if it's possible here. — Maxim Naumov, Jul 20 '18 at 08:53
@Alan you could, but why on earth would you recommend such a bad idea? :) — Thom A, Jul 20 '18 at 08:59
@MitchWheat I don't disagree, just not for something that can be achieved in a dataset approach. Cursors should really only be used for iterative tasks, not things like a running total. — Thom A, Jul 20 '18 at 09:20
@MitchWheat . . . I'm curious how you can call this a duplicate of anything. I cannot figure out what arithmetic manipulations the OP is representing in the sample results. It is not a cumulative sum (although that might be part of the solution). — Gordon Linoff, Jul 20 '18 at 11:11

score 1 · Accepted Answer · answered Jul 20 '18 at 09:03

DECLARE @T TABLE (ROW_NUM INT, AMOUNT MONEY,N_AMOUNT MONEY, NEEDED_RESULT MONEY)
INSERT INTO @T 
SELECT * FROM (VALUES
(1,       100.00, NULL     ,100),
(2,       300.00, NULL     ,200),
(3,       300.00, -400.00  ,0  )
) a(ROW_NUM , AMOUNT ,N_AMOUNT , NEEDED_RESULT )


;WITH x AS 
(
    SELECT 
        *,
        [R] = SUM(Amount)  OVER (ORDER BY ROW_NUM DESC) + SUM(N_Amount) OVER ()
    FROM @T
)
SELECT ROW_NUM,AMOUNT,N_AMOUNT, NEEDED_RESULT,
    CASE 
        WHEN R < 0 THEN 0  
        WHEN R > Amount THEN Amount
        ELSE R
    END
FROM x
ORDER BY ROW_NUM

MSSQL distribute negative amount to positive amount rows

1 Answers1