By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
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 |