select distinct tempcheck_id, record_id
from #tempcheck
inner join
	(select tempcheck_id, min(record_id) as min_id
	from #tempcheck
	group by tempcheck_id) Sub
	on main.tempcheck_id = sub.tempcheck_id
	and main.record_id = sub.min_id
except
select t1.tempcheck_id, t1.record_id
from #tempcheck t1
inner join #tempcheck t2
	on t1.record_id = t2.record_id
	and t1.tempcheck_id < t2.tempcheck_id
	and t1.item = t2.item
	and t1.i_desc = t2.i_desc
	and t1.s_desc = t2.s_desc
	and t1.m_desc = t2.m_desc
inner join #tempcheck t3
	on t1.record_id = t3.record_id
	and t2.tempcheck_id < t3.tempcheck_id
	and t1.item = t3.item
	and t1.i_desc = t3.i_desc
	and t1.s_desc = t3.s_desc
	and t1.m_desc = t3.m_desc

select 
	A.*,
	B.*,
	Case 
		When A.item = B.item and A.I_desc = B.I_desc and A.m_desc = B.m_desc then 'Type 2'
		When A.I_desc = B.I_desc and A.m_desc = B.m_desc then 'Type 1'
		When A.item = B.item and A.m_desc = B.m_desc then 'Type 3'
		Else 'No Match'
	End as status_flag
From 
	#tempcheck A  join
	#tempcheck B On
		A.record_id = B.record_id And
		A.tempcheck_id > B.tempcheck_id
Order by A.tempcheck_id

;with JoinMath as (
      Select a.recordid, a.tempcheckid, 
                    case when A.I_desc = B.I_desc then 1 else 0 end + 
                    case when A.m_desc = B.m_desc then 1 else 0 end + 
                    case when A.item = B.item then 1 else 0 end as Matches,
                         --create a pseudo bitbucket
                    case when A.I_desc = B.I_desc then 1 else 0 end +                           
                    case when A.m_desc = B.m_desc then 2 else 0 end + 
                    case when A.item = B.item then 1 else 0 end as MatchFilter
      From #tempcheck a
              inner join #tempcheck b on a.recordID=b.recordID and a.tempcheckID<>a.tempcheckid
      )
Select *,
            case 
                    when MatchFilter & 1 = 0 then 'Type1'
                    when MatchFilter & 2 = 0 then 'Type4'
                    when MatchFilter & 4 = 0 then 'Type3'
                    ELSE 'Type2'
            end as MatchType
 from JoinMath
 where Matches>1