The logic seems a little hard to follow. You seem to want conditional logic based on whether or not the sum is 0
for the 'STD'
values.
This should return what you want on the data you provided:
select id, category, 'BDSTD' as agingSet, amount
from (select t.*,
row_number() over (partition by id order by category) as seqnum,
sum(case when agingSet = 'Std' and amount <> 0 then 1 else 0 end) over (partition by id) as num_not_zero
from t
) t
where (num_not_zero = 0 and seqnum = 1) or
(num_not_zero > 0 and agingSet = 'BDSTD');
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…