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

I have a table like this,

id  date        start       end         productid   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.

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

id  date        start       end         productid   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.

CodePudding user response：

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.

CodePudding user response：

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