MIN() in JOIN based of dates

Question

I would like to join two tables based on two common fields. Assume the date is 2024 I want to join the sale table on dictionary table which hold only articles with dates 2022 and 2023. Because of the record in sale table which has sale date in 2024 I cannot get match with the dictionary which holds value only for years 2022,2023. I cannot add new rows to dictionary table.

I thought I could solve this with MIN() on dates in join but I am not sure how plus I cannot use MIN() in JOIN.

create table dict (setcode string, year numeric);
create table sale (setcode string, datum date);

insert into dict
values ('S201',2022), ('S202',2022),('S201',2023), ('S202',2023);

insert into sale
values ('S201', '2022-05-05'), ('S201', '2023-04-04'), ('S201', '2024-06-06');


select * from  sale a
  join dict b
 on cast(FORMAT_DATE("%Y",a.datum) as integer)=b.year
and  a.setcode=b.setcode
where cast(FORMAT_DATE("%Y",a.datum) as integer) >= 2022;

Any suggestions how to get the joined table with match on sales in 2024 with dictionary holding values for year 2023? I want to track the sale in 2024 based on dictionary which does not hold value for 2024 like this:

 a.setcode   a.datum      b.setcode    b.year  
 S201        2022-05-05   S201         2022 
 S201        2023-04-04   S301         2023 
 S201        2024-06-06   S301         2023

Answer 1

Add date condition when joining your tables as follows :

select *
from  sale a
join dict d on a.setcode = d.setcode 
            and cast(FORMAT_DATE("%Y",a.datum) as integer) <= d.year;

Results :

setcode datum      setcode_1 year
S201    2022-05-05 S201      2022
S201    2022-05-05 S201      2023
S201    2023-04-04 S201      2023