SQL - learn (random)

sql.sql

select *
from (
	values
		(1, 1, 156.32, 1)
		,(null, 2, 468.96, 1)		
) s (r1, r2, amount, id)

select *
from (
	values
		(1, NULL, 64.87, 1)
		--,(NULL, NULL, 13.46, 1)
		,(NULL, 1, 1.02, 1)
		,(NULL, 2, 3.05, 1)
		,(NULL, NULL, -486.96, 1)		
) f (r1, r2, amount, id)


;with salary as (
	select *
	from (
		values
			(1, 1, 156.32, 1)
		,(null, 2, 468.96, 1)		
	) s (r1, r2, amount, id)
)
,fringe as (
	select *
	from (
		values
			(1, NULL, 64.87, 1)
			--,(NULL, NULL, 13.46, 1)
			,(NULL, 1, 1.02, 1)
			,(NULL, 2, 3.05, 1)
			,(NULL, NULL, -486.96, 1)		
	) f (r1, r2, amount, id)
)

select		
s.*
--, f.*
,isR1 = sum(case when s.r1 = f.r1 then f.amount else 0 end)
,isR2 = sum(case when s.r2 = f.r2 then f.amount else 0 end)

from salary s
left join fringe f on f.id = s.id					
					and (
								case when s.r1 = f.r1 then f.amount else null end is not null
							or  case when s.r2 = f.r2 then f.amount else null end is not null
						)
group by s.r1, s.r2, s.amount, s.id

r1 r2 amount id
1 1 156.32 1
NULL 2 468.96 1