Script to compare Data in Rows

drop table #t create table #t (id varchar(6) , [year] varchar(4), membertype varchar(4))insert into #t (id, [year], membertype)select
select
select
select
select
;WITH CTE1 AS(SELECT
)SELECT a.id,CASE WHEN a.membertype <> b.membertype THEN 'changed' ELSE 'not changed' END AS status,a.membertype AS current_membertype, b.membertype AS prev_member_type
FROM cte1 a
INNER JOIN cte1 b ON a.id = b.id WHERE a.year_rank = 1 AND b.year_rank = 2AND a.membertype <> b.membertype *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY YEAR DESC) AS year_rank FROM #t '123456', '2015', 'Yes' union '123456', '2009', 'No' '123457', '2016', 'No' union '123457', '2011', 'Yes' union '123456', '2010', 'Yes' union