I won't say that this is the best way to do it, but I believe a simple self join on record_id with a case statement will work:

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