Computus - Easter Sunday in SQL Server 2005

Working on an article about an auxiliary calendar table I needed to find a routine for calculating Easter Sunday. Many years ago I made my Delphi routine for this, and haven't really thought much about it since. Reading an article about calendar tables I found a function doing the trick. I tested the routine and it happened to fail so I needed to find another one. Luckily today we have wikipedia, something we didn't 10 years ago when I found my last Easter Sunday function.

From Wikipedia: Computus (Latin for computation) is the calculation of the date of Easter in the Christian calendar.
In the article I found the Meeus/Jones/Butcher Gregorian algorithm that is supposed to work for any Easter ever in the Gregorian calendar.

CREATE FUNCTION [Auxiliary].[Computus]
(
@Y INT -- The year we are calculating Easter Sunday for
)
RETURNS DATETIME
AS
BEGIN
DECLARE
@a INT,
@b INT,
@c INT,
@d INT,
@e INT,
@f INT,
@g INT,
@h INT,
@i INT,
@k INT,
@L INT,
@m INT

SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b - @f + 1) / 3
SET @h = (19 * @a + @b - @d - @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i - @h - @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L - 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L - 7 * @m + 114) % 31))
END

I got no clue about the intricacy of this math. But it works and that's fine for me :-)

Next: "The Auxiliary Calendar Table For SQL Server 2005" ...

Auxiliary roundup - The SQL table valued for loop function?

After creating the numbers CTE in my last post I wanted to go on building my standard auxiliary schema. First of all lets create the schema. Quite simple:

CREATE SCHEMA [Auxiliary]

A schema in SQL server 2005 is a collection of database entities that form a single namespace.
I now got a place to put auxiliary entities.

First off, I decided to put my Numbers CTE into a table valued function. I wanted to make it a bit flexible by giving the startnumber, endnumber and increment as parameters.
Incidentally this suddenly looked a little bit like the For(i=1; i <=1000; i++) construct in C and C#

CREATE FUNCTION Auxiliary.Numbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS @RetNumbers TABLE
(
[Number] int PRIMARY KEY NOT NULL
)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END;

To use this to retrieve a list of numbers between 10 and 1000 incremented by 10:

SELECT Number FROM Auxiliary.Numbers(10,1000,10)

This will return:

Number
-----------
10
20
30
...
980
990
1000

(100 row(s) affected)

For a large number of numbers this function is quite slow. An inlined version of this performs much better, but you need to specify the MAXRECURSION hint in the statement using the function:

CREATE FUNCTION Auxiliary.iNumbers
(
@AFrom INT,
@ATo INT,
@AIncrement INT
)
RETURNS TABLE
AS
RETURN(
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
SELECT n AS Number from Numbers
)

To use this to retrieve a list of numbers between 10 and 1000 incremented by 10:

SELECT Number FROM Auxiliary.iNumbers(10,1000,10)

If your "table" will return more than 101 numbers (100 recursions):

SELECT Number FROM Auxiliary.iNumbers(1,10000,1)
OPTION(MAXRECURSION 10000)

If you need really many numbers then you definitely should build a permanent numberstable.
Louis Davidson wrote a nice post a about it here.