SQLTeam.com | Weblogs | Forums

Expression that Check if info is the same in mutiple columns then return a value

I have a sql table with the below info

Source,year,id,pay,gross (this is the column names)
Delta,89,123,67,4500. (Data below)

What I want to do create a 6th column name category that checks if Id , year and gross are the same then the category should say. Match else no match
I should be able to do a case statement but how do I do this for a table with over 1k records
With mutiple years . The table is created by a union all statement so yu will always have 2 of the same ids on the same year however the gross may or may not be the same . I would prefer a ssrs expression for the category column if not a SQL case statement will do
End result should be

Delta ,89,123,67,4500,match
Dept,98,435,87,674,no match
Home,98,435,99,886,no match

using a sample table

create table #dstyles(Name varchar(50), 
                     _year int,
					 Id int,
					 pay money,
					 gross money)

insert into #dstyles
select 'Delta' ,89,123,67,4500 union
select 'Beta',89,123,65,4500 union
select 'Dept',98,435,87,674 union
select 'Home',98,435,99,886 

--this is the key part for you to use
select * , 
		    when dup._count > 1 then 'match' 
			else 'no match'
		 end as category
  from #dstyles d
    join (
			select count(1) _count, id, _year, gross
		     from #dstyles d
			 group by id, _year, gross
	) dup on d.Id = dup.id and d._year = dup._year and d.gross = dup.gross

drop table #dstyles