Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_a
  (name        character varying(10),
   asof_yrmo   integer);
create table table_b
  (name       character varying(10),
   comp       character varying(1),
   asof_yrmo  integer);
insert into table_a (name, asof_yrmo)
 SELECT 'Will'   ,    202101 UNION ALL
 SELECT 'James'  ,    202101 UNION ALL
 SELECT 'Samuel' ,    202101 UNION ALL
 SELECT 'John'   ,    202101 UNION ALL
 SELECT 'George' ,    202101 UNION ALL
 SELECT 'Will'   ,    202012 UNION ALL
 SELECT 'James'  ,    202012 UNION ALL
 SELECT 'Samuel' ,    202012 UNION ALL
 SELECT 'John'   ,    202012 UNION ALL
 SELECT 'George' ,    202012;
10 rows affected

insert into table_b (name, comp, asof_yrmo)
 SELECT 'Will'   , 'Y'    ,    202101 UNION ALL
 SELECT 'James'  , 'Y'    ,    202101 UNION ALL
 SELECT 'Samuel' , 'Y'    ,    202101 UNION ALL
 SELECT 'John'   , 'N'    ,    202101 UNION ALL
 SELECT 'George' , 'N'    ,    202101 UNION ALL
 SELECT 'Will'   , 'N'    ,    202012 UNION ALL
 SELECT 'James'  , 'N'    ,    202012 UNION ALL
 SELECT 'Samuel' , 'Y'    ,    202012 UNION ALL
 SELECT 'John'   , 'Y'    ,    202012 UNION ALL
 SELECT 'George' , 'Y'    ,    202012 UNION ALL
 SELECT 'George' , 'Y'    ,    201901 UNION ALL
 SELECT 'James'  , 'N'    ,    201901 UNION ALL
 SELECT 'John'   , 'N'    ,    201901 UNION ALL
 SELECT 'Samuel' , 'Y'    ,    201901 UNION ALL
 SELECT 'Will'   , 'N'    ,    201901;
15 rows affected

select a.name,
       a.asof_yrmo,
       b.comp,
       bb.comp AS comp_from_previous_two_years
  from table_a as a 
  left join table_b as b 
    on b.name = a.name and
       b.asof_yrmo = a.asof_yrmo
  LEFT OUTER JOIN table_b AS BB
    ON bb.name = a.name AND
       bb.ASOF_YRMO = b.ASOF_YRMO - 200
  where a.asof_yrmo = (select max(s60.asof_yrmo) 
                         from table_a as s60)
  ORDER BY a.name


name
asof_yrmo
comp
comp_from_previous_two_years


George
202101
N
Y

James
202101
Y
N

John
202101
N
N

Samuel
202101
Y
Y

Will
202101
Y
N
name	asof_yrmo	comp	comp_from_previous_two_years
George	202101	N	Y
James	202101	Y	N
John	202101	N	N
Samuel	202101	Y	Y
Will	202101	Y	N