Months in BigQuery

Question

I hope someone can help me.

How to do this in BigQuery SQL?

If for example, I have the date range from October 30 to November 05, the WB Date will be Oct 30 as it is the Monday, I want another field name Month on which the result is 11 or November.

The result should be November because there are only 2 dates (Oct 30 and 31) that belong to Oct and the rest are November.

So, if the given week date range has more dates that belong to whichever month, the result would be the dominant month.

Date WB Monday	Month
Oct 30, 2023	November
Nov 06, 2023	November

Updates: I managed to solve it. Here is the SQL that I used.

SELECT

  MIN(Week_Beginning) AS wb_date,

  (SELECT EXTRACT(MONTH FROM date) AS most_common_month
   FROM UNNEST(GENERATE_DATE_ARRAY(Week_Beginning, DATE_ADD(Week_Beginning, INTERVAL 6 DAY), INTERVAL 1 DAY)) AS date
   
GROUP BY most_common_month
   
ORDER BY COUNT(*) DESC

LIMIT 1) AS dominant_month

FROM
  `table_name`
  where Week_Beginning BETWEEN '2023-01-01' AND '2024-12-31'
  
GROUP BY
  Week_Beginning
ORDER BY Week_Beginning asc

Answer 1

SELECT
  MIN(Week_Beginning) AS wb_date,
  (SELECT EXTRACT(MONTH FROM date) AS most_common_month 
   FROM UNNEST(
          GENERATE_DATE_ARRAY(
                Week_Beginning, 
                DATE_ADD(Week_Beginning, INTERVAL 6 DAY), INTERVAL 1 DAY
          )
        ) AS date

   GROUP BY most_common_month
   ORDER BY COUNT(*) DESC
   LIMIT 1) AS dominant_month
FROM table_name 
WHERE Week_Beginning BETWEEN '2023-01-01' AND '2024-12-31'
GROUP BY Week_Beginning 
ORDER BY Week_Beginning asc

I used the one above and it works. Thank you for those who tried to look into it.

Answer 2

Consider below approach

select Week_Beginning, 
  if(date_diff(last_day(day, month), day, day) > 2, month[0], month[1]) as Month
from your_table, 
unnest([parse_date('%b %d, %Y', Week_Beginning)]) day,
unnest([struct([format_date('%B', day), format_date('%B', 7 + day)] as month)])     

if applied to sample data like in your question - output is