By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU3) (KB4538853) - 15.0.4023.6 (X64) Mar 4 2020 00:59:26 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
declare @t table(id int, foreignkey int, value1 int, value2 int)
insert into @t select 1 , 500 , -100 , 0
insert into @t select 2 , 500 , 900 , 15
insert into @t select 3 , 500 , 570 , 25
insert into @t select 4 , 999 , 100 , 57
insert into @t select 5 , 999 , 150 , 0
insert into @t select 5 , 111 , 150 , 100
select * from @t
;with cte as
(
select id, foreignkey, value1, value2,dense_rank() over (partition by foreignkey order by (case when value2 = 0 then 0 else 1 end)) as rn
from @t t1
)
,cte2 as
(
select t1.id, t1.foreignkey, t1.value1+isnull(t2.value1,0) as value1, t1.value2
from cte t1
left join cte t2 on (t2.foreignkey = t1.foreignkey and t1.rn<> t2.rn)
)
select * from cte2
where value2 <> 0
| id | foreignkey | value1 | value2 |
|---|---|---|---|
| 1 | 500 | -100 | 0 |
| 2 | 500 | 900 | 15 |
| 3 | 500 | 570 | 25 |
| 4 | 999 | 100 | 57 |
| 5 | 999 | 150 | 0 |
| 5 | 111 | 150 | 100 |
| id | foreignkey | value1 | value2 |
|---|---|---|---|
| 5 | 111 | 150 | 100 |
| 2 | 500 | 800 | 15 |
| 3 | 500 | 470 | 25 |
| 4 | 999 | 250 | 57 |