Postgres SQL Query - If table 1 date does not match table 2 date then grab the max date available fr-CodePudding

In the picture below, Table 1 and 2 are made up of subqueries full of proprietary fields. I'm trying to figure out the best way to produce the query output on the right but am not sure if I should be writing a boolean statement in the JOIN clause or in the WHERE clause. Even then, I'm not aware of a good CTE or CASE statement that would get the job done.

The only solution I can think of is to take the existing query and convert the whole thing into yet another subquery where I just take the MAX() available History Date by means of a Cartesian Join.

How do I produce the query in the best way?

This

CodePudding user response：

Use a lateral join to select the max(history_date) date which is less than or equal the week_start_date.

select t1.week_start_date, t2.history_date  
   from table1 t1  
   join lateral (select max(history_date) history_date 
                   from table2  
                   where history_date <= t1.week_start_date
                ) t2 
             on true;

NOTE: Not tested. I cannot copy/past data from an image.