Determine Easter Sunday BigQuery

Question

I am new to BigQuery, but have managed to get the following script to correctly calculate Easter Sunday for a given year, using the logic I got from Carter https://www.rmg.co.uk/stories/topics/when-easter#:~:text=The%20simple%20standard%20definition%20of,Easter%20is%20the%20next%20Sunday.

DECLARE year INT64;
DECLARE D INT64;
DECLARE E INT64;
DECLARE Q INT64;
DECLARE easter_date STRING;

-- Set year
SET year = 1998; -- Replace with the desired year

-- Step 1: Calculate D = '225' - 11 * (year MOD 19)
SET D = 225 - 11 * MOD(CAST(year AS INT64), 19);

-- Step 2: If D is greater than 50 then subtract multiples of 30 until the resulting new value of D is less than 51
WHILE D > 50 DO
SET D = D - 30;
END WHILE;

-- Step 3: If D is greater than 48 subtract 1 from it
IF D > 48 THEN
SET D = D - 1;
END IF;

-- Step 4: Calculate E = (year + CAST(FLOOR(year / 4) AS INT64) + D + 1) MOD 7
SET E = MOD(year + CAST(FLOOR(year / 4) AS INT64) + D + 1, 7);

-- Step 5: Calculate Q = D + 7 - E
SET Q = D + 7 - E;

-- Step 6: Determine the date of Easter Sunday
IF Q < 32 THEN
SET easter_date = CONCAT(CAST(year AS STRING), '-03-', CAST(Q AS STRING) );
ELSE
SET easter_date = CONCAT(CAST(year AS STRING), '-04-', CAST(Q - 31 AS STRING) );
END IF;

-- Output the result
SELECT CAST(easter_date AS DATE) AS easter_sunday;

As mentioned the script works great, however I am in the process of building a Holidays script, and need to incorporate this calculation in it. I am trying to avoid creating a function, but getting this logic into some kind of CTE, is proving difficult - I assume this is because of the loops I needed to write? Essentially I want to get Easter Sunday for a range of years (1990 - 2099), that I can add to my current date table.

Any assistance / advice please?

Answer 1

Instead of using a procedural language, you can use linear calculations to calculate more than 1 year in a single query:

WITH
t1 as
(
  select year, 225 - 11 * MOD(year, 19) as d, -- 203
  from unnest(generate_array(1900, 2100)) as year
),
t2 as
(
  select
    year,
    mod(d - 21, 30) + 21 as d
  from t1
),
t3 as
(
  select 
    year,
    if(d > 48, d-1, d) as d 
  from t2
),
t4 as
(
  select 
    year, d,
    D + 7 - 
      MOD(year + CAST(FLOOR(year / 4) AS INT64) + D + 1, 7) -- this line calculates e
    as q
  from t3
),
easter_list as
(
  SELECT
    year, d,
    date(date("2000-03-01") + interval (q-1) day + interval (year - 2000) year) as x
  from t4
)
select *
from easter_list