CODEWITHSUNDEEP
sqlsql-servert-sqlmath
Maxim Naumov
Maxim Naumov

Reputation: 23

MSSQL distribute negative amount to positive amount rows

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

Upvotes: 0

Views: 85

Answers (1)

Stanislav Kundii
Stanislav Kundii

Reputation: 2894

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

Upvotes: 1

Related Questions