Copy the value of the column added via left join to the previous 1 row and the following 2 rows

Question

I am trying to copy the value of the column added via left join to the previous 1 row and the following 2 rows.

I created a helper table with numeric yearmonth_IDs for each yearmonth to solve the problem during the join. (yearmonth cannot be used for this purpose because it is not continious; for instance 202301-1=202300; and this is not returning a valid yearmonth.)

What is an alternative solution using other built-in functions instead of using a helper table/column, as I've done?

Data:

drop table if exists public.left_table;
create table public.left_table (contractid integer, yearmonth varchar(6), desired boolean);
insert into public.left_table (contractid, yearmonth, desired)
values
    (1, 202201, false), (1, 202202, true), (1, 202203, true), (1, 202204, true),    (1, 202205, true), (1, 202206, false), (1, 202207, false),
    (2, 202210, false), (2, 202211, false), (2, 202212, true), (2, 202301, true), (2, 202302, true), (2, 202303, true), (2, 202304, false);
select * from left_table;

drop table public.right_table;
create table public.right_table (contractid integer, yearmonth varchar(6), flag boolean);
insert into public.right_table (contractid, yearmonth, flag)
values
    (1, 202203, true),
    (2, 202301, true);
select * from right_table;

My solution:

drop table if exists yearmonth_ids;
create table yearmonth_ids as
select row_number() over() yearmonth_id, to_char(x, 'YYYYMM') yearmonth
from generate_series('2018-01-01', current_date, '1 month') x;

select * from yearmonth_ids;

select 
    lt.*,
    coalesce(rt.flag, false) flag
from 
    (select l.*, y.yearmonth_id from left_table l left join yearmonth_ids y on l.yearmonth = y.yearmonth) lt
left join 
    (select r.*, y.yearmonth_id from right_table r left join yearmonth_ids y on r.yearmonth = y.yearmonth) rt
on lt.contractid = rt.contractid and lt.yearmonth_id between rt.yearmonth_id-1 and rt.yearmonth_id+2
order by lt.contractid, lt.yearmonth;

Answer 1

No need for helper table, you can operate on dates like here:

select l.contractid, l.yearmonth, l.desired, coalesce(r.flag, false) flag
from left_table l
left join right_table r on to_date(l.yearmonth, 'yyyymm') 
  between to_date(r.yearmonth, 'yyyymm') - interval '1' month
      and to_date(r.yearmonth, 'yyyymm') + interval '2' month

dbfiddle demo