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?