How to create MIN and MAX date columns from an INNER JOIN across two DATE columns where each DATE column is from a separate table BigQuery

Question

This is a problem that's driving me mad!

I want to create one MIN column and one MAX column for each unique person_id from an inner join across three tables that has two separate DATE columns - one in tbl1 and one in tbl2 (table 3 just joins based on the person_id). I'm sure it's just the order I'm writing my query but I cannot figure out where to place the DATE column from tbl2.

In short, whatever the MIN date is from the combined DATE columns from tbl1 and tbl2 will form start_date and whatever the MAX is will form end_date. Both are DATES, both in the format: YYYY-MM-DD, so no concerns there.

Here's an example query that works when I just want to find the MAX and MIN date for the tbl1 DATE column:

SELECT  tbl1.person_id, MIN(tbl1.Date) AS start_date, MAX(tbl1.Date) AS end_date,
FROM `database_name.table_name1` tbl1
inner join `database_name.table_name2` tbl2
ON (
tbl1.person_id = tbl2.person_id
)
inner join `database_name.table_name3` tbl3
ON (
tbl1.person_id = tbl3.person_id    
)
GROUP BY tbl1.person_id

Thank you in advance

Answer 1

You seem to want least() and greatest():

SELECT tbl1.person_id,
       LEAST(MIN(tbl1.Date), MIN(tbl2.Date)) AS start_date,
       GREATEST(MAX(tbl1.Date), MAX(tbl2.Date)) AS end_date,
FROM `database_name.table_name1` tbl1 JOIN
     `database_name.table_name2` tbl2
     ON tbl1.person_id = tbl2.person_id JOIN
     `database_name.table_name3` tbl3
     ON tbl1.person_id = tbl3.person_id    
GROUP BY tbl1.person_id