mjhjmtu

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.

DML

;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

You can accomplish what you're after using window functions, but I can't make any promises about performance if you're running this query over a lot of rows. The idea is to calculate the sum, minimum, and an unordered row number for every partition. Keep all rows with a minimum > 0 but only keep the first row of a partition if the minimum < 0.

-- put data into temp table for illustration purposes
select accountId, type, amount into #t220618
from (
 values
 (1, 'R', -10)
 ,(1, 'R', 30)
 ,(1, 'V', 10) 
 ,(2, 'R', 20)
 ,(2, 'R', -5)
 ,(2, 'V', 5)
 ,(3, 'R', 20)
 ,(3, 'R', 30)
) x (accountId, type, amount);


SELECT
 accountId
, type
, CASE WHEN part_min < 0 THEN part_sum else amount END amount
FROM (
 SELECT
 accountId
 , type
 , amount
 , SUM(amount) OVER (PARTITION BY accountId, type) part_sum
 , MIN(amount) OVER (PARTITION BY accountId, type) part_min
 , ROW_NUMBER() OVER (PARTITION BY accountId, type ORDER BY (SELECT NULL)) part_rn
 FROM #t220618
) q
WHERE q.part_min > 0 OR (part_min < 0 AND part_rn = 1);