Bigquery - how to make a column true when month of date column matches with other date column? [closed]

Question

I have a table like this,

id  date        start       end         product     result

10  2022-06-10  2022-02-10  2022-06-12  183         TRUE
10  2022-06-11  2022-02-10  2022-06-12  183         TRUE
10  2022-06-12  2022-02-10  2022-06-12  183         TRUE
10  2022-06-13  null        null        183         FALSE
10  2022-06-14  null        null        183         FALSE
......
10  2022-04-01  null        null        183         FALSE 

So, I want the column 'result' as TRUE when year and month of 'date' column matches the year and month of 'end' column with null values.

i.e for each date (month and year) in 'date' column, look through all rows in 'end' column with same month and year and if the month and year matches, then make frozen as TRUE.

i.e, The final result should look like this,

id  date        start       end         product     result

10  2022-06-10  2022-02-10  2022-06-12  183         TRUE
10  2022-06-11  2022-02-10  2022-06-12  183         TRUE
10  2022-06-12  2022-02-10  2022-06-12  183         TRUE
10  2022-06-13  null        null        183         TRUE
10  2022-06-14  null        null        183         TRUE
......
10  2022-07-01  null        null        183         FALSE 

Any help would be appreciated.

Answer 1

Please consider the below approach for bigquery.

select *,
case
when date is not null and 
end is not null and 
date_trunc(date,month) = date_trunc(end,month)
then "TRUE" 
else "FALSE" 
end as New_Result
from Data

I've added in a check for nulls to return a false result if either date or end has a null value, but you could seperate this to flag these instances if needed like below.

select *,
case
    when date is null or end is null then "CHECK ME"
    when date_trunc(date,month) = date_trunc(end,month) then "TRUE" 
    else "FALSE" 
end as New_Result,
from Data

Answer 2

What about using alias when querying:

SELECT id, date, start, end, productid, (date=end) as result FROM ProductTable;

Hereby you will get the boolean result of date=end as the last column, when you query.