SQL Query: Perform a join + group by + date condition

Question

I have two tables with date information. I want to join both tables on the condition that the date specified in Table_Y lies in between the minimum and maximum date of TABLE_X.

I have the following query where I determine the minimum and maximum dates in TABLE_X and perform a join to include information of both tables. How should I go about including a statement to only include the rows where bb.date between aa.min_startdate and aa.max_startdate? Note that the date column of TABLE_Y is not specified in the current query as I do not want to include it in the final table.

SELECT
  aa.column_1,
  bb.column_2,
  bb.column_3,
  bb.column_4,
  MIN(aa.startdate) AS min_startdate,
  MAX(COALESCE(aa.enddate, CURRENT_DATE)) AS max_startdate

FROM TABLE_X AS aa

INNER JOIN TABLE Y AS bb
        ON bb.column_2 = aa.column_2
       AND bb.column_3 = aa.column_3

GROUP BY 1, 2, 3, 4

I was thinking of something in the order of a WHERE or HAVING function in combination with bb.date between aa.min_startdate and aa.max_startdate but this only results in errors due to the aggregate functions.

Answer 1

I have two tables with date information. I want to join both tables on the condition that the date specified in Table_Y lies in between the minimum and maximum date of TABLE_X.

I would suggest something like this:

select . . . 
from table_y y join
     (select x.*, min(start_date) over () as min_start_date,
             max(start_date) over () as max_start_date
      from x
     ) x
     on y.date between x.min_start_date and x.max_start_date;

Your sample query seems to have other conditions that you have not described. This answers the question that you have asked.