I have a need to sum values together only if there is a negative value present in a partition. If there are no negative values in a partition, it should just output the row.
Here's what I have right now. Initial data is provided as a CTE.
;with ledger as ( select accountId, type, amount from ( values (1, 'R', -10) ,(1, 'V', 10) ,(1, 'R', 30) ,(2, 'R', 20) ,(2, 'R', -5) ,(2, 'V', 5) ,(3, 'R', 20) ,(3, 'R', 30) ) x (accountId, type, amount) ) ,b as ( --identifies accountid, type with negatives select accountid ,type from ledger group by accountid, type having min(amount) < 0 ) ,onlyPositives as ( select l.accountid ,l.type ,l.amount from ledger l left join b on b.accountid = l.accountid and b.type = l.type where b.accountid is null ) ,aggregatedNegatives as ( select l.accountid ,l.type ,amount = sum(l.amount) from ledger l inner join b on b.accountid = l.accountid and b.type = l.type group by l.accountid, l.type ) select accountid, type, amount from onlyPositives union all select accountid, type, amount from aggregatedNegatives
I'm expecting an output like this, and the query above outputs correctly.
1, R, 20 (summed because -10+30=20) 1, V, 10 (left alone) 2, R, 15 (summed because 20-5=15) 2, V, 5 (left alone) 3, R, 20 (left alone) 3, R, 30 (left alone)
The query so far looks beastly and feels unnecessarily complex. Is there a simpler query I could write that I have overlooked?
This is a slight variation on the issue: If positive, sum all items. If negative, return each one
rextester -> https://rextester.com/EZRT33825