Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_a
    ("name" varchar(6), "asof_yrmo" int)
;
    
INSERT INTO table_a VALUES
    ('Will', 202101),
    ('James', 202101),
    ('Samuel', 202101),
    ('John', 202101),
    ('George', 202101),
    ('Will', 202012),
    ('James', 202012),
    ('Samuel', 202012),
    ('John', 202012),
    ('George', 202012);
    
SELECT * FROM table_a;


CREATE TABLE table_b
    ("name" varchar(6), "comp" varchar(1), "asof_yrmo" int)
;
    
INSERT INTO table_b VALUES
    ('Will', 'Y', 202101),
    ('James', 'Y', 202101),
    ('Samuel', 'Y', 202101),
    ('John', 'N', 202101),
    ('George', 'N', 202101),
    ('Will', 'N', 202012),
    ('James', 'N', 202012),
    ('Samuel', 'Y', 202012),
    ('John', 'Y', 202012),
    ('George', 'Y', 202012);
    
SELECT * FROM table_b;
10 rows affected



name
asof_yrmo


Will
202101

James
202101

Samuel
202101

John
202101

George
202101

Will
202012

James
202012

Samuel
202012

John
202012

George
202012


10 rows affected



name
comp
asof_yrmo


Will
Y
202101

James
Y
202101

Samuel
Y
202101

John
N
202101

George
N
202101

Will
N
202012

James
N
202012

Samuel
Y
202012

John
Y
202012

George
Y
202012


SELECT 
    *
FROM table_a a
JOIN table_b b ON a.name = b.name AND a.asof_yrmo = b.asof_yrmo


name
asof_yrmo
name
comp
asof_yrmo


George
202012
George
Y
202012

George
202101
George
N
202101

James
202012
James
N
202012

James
202101
James
Y
202101

John
202012
John
Y
202012

John
202101
John
N
202101

Samuel
202012
Samuel
Y
202012

Samuel
202101
Samuel
Y
202101

Will
202012
Will
N
202012

Will
202101
Will
Y
202101


SELECT 
    a.name,
    MAX(a.asof_yrmo) as asof_yrmo,
    MAX(comp) FILTER (WHERE a.asof_yrmo = '202101') as comp,
    MAX(comp) FILTER (WHERE a.asof_yrmo = '202012') as comp_from_prev_two_years
FROM table_a a
JOIN table_b b ON a.name = b.name AND a.asof_yrmo = b.asof_yrmo
GROUP BY a.name


name
asof_yrmo
comp
comp_from_prev_two_years


George
202101
N
Y

James
202101
Y
N

John
202101
N
Y

Samuel
202101
Y
Y

Will
202101
Y
N
name	asof_yrmo
Will	202101
James	202101
Samuel	202101
John	202101
George	202101
Will	202012
James	202012
Samuel	202012
John	202012
George	202012