I need Dynamic query in BigQuery to find on which Date the Day Saturday has occurred in First week o-CodePudding

I need Dynamic query in BigQuery to find Date of Day Saturday has occurred in First week of Year 2021.

Consider the Day on date of 2022-01-01 is Saturday. Therefore, I want to extract the date of last Year first week on which the Saturday was occurred.

CodePudding user response：

Try the following:

with sample_dates as (
    select date
    from unnest(generate_date_array('2022-01-01','2022-12-31')) as date
)
select 
    date
    ,(select last_year_date
      from unnest(generate_date_array(date_sub(date, INTERVAL 1 YEAR), date-1)) as last_year_date
      where extract(week from date) = extract(week from last_year_date)
      and extract(dayofweek from date) = extract(dayofweek from last_year_date)  
    ) as last_year_date
from sample_dates 
;

Given all of 2022 dates, this provides the same day of week for the same week in the previous year.

For example 2022-01-01 results in 2022-01-02 which is the first Saturday of the first week of last year.

If you're only looking for a single date the main portion would be the generating the appropriate date range with the generate_date_array function and then filtering based on the same week number and dayofweek value.

CodePudding user response：

Use below - should work for any year - just replace 2021 with whatever year you need

select date(2021, 1, 8 - extract(dayofweek from date(2021, 1, 1)))