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.

SQL Numbers table using a Common table expression (CTE)

With SQL server 2005 Microsoft finally implemented recursive SQLs as described in the ISO-ANSI SQL3 standard (Implemented in DB2, Sybase and partly Oracle a "loooong" time ago).
This new and exciting feature dubbed CTE (Common table expression) is supposed to be particular useful for traversing trees and hierarchies using SQL.
Reading Joe Celcos SQL Programming style (Highly recommended) I came acorss a concept called "numbers table".
This is a table with a sequence of integer numbers. Such a table is quite useful for parsing strings and generating testdata.
Thinking of uses for CTE's I figured they could be used to create an ad-hoc numbers table. Such a construct will also serve as an easy to understand example of a recursive query.

WITH Numbers(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < 1000
)
SELECT n from Numbers
OPTION(MAXRECURSION 1000) -- defaults 100

What happens in a recursive query is that the first select statement initializes the query and returns the first row(s),
the second query then executes for every row added to the result (Selecting from itself)
Thus selecting n incremented by one and adding the row(s) to the result.

To parse a string you could do it like this:


DECLARE @Delimitedtext varchar(max);
DECLARE @Delimiter char(1);
SET @Delimitedtext = ',aaa,bbbb,cccc,';
SET @Delimiter = ',';
WITH Numbers(n)
AS
(
SELECT 1 AS n
UNION ALL
SELECT (n + 1) AS n
FROM Numbers
WHERE
n < 1000 )
SELECT
SUBSTRING(@DelimitedText, Numbers.n + 1, CHARINDEX(@Delimiter, @DelimitedText,Numbers.n + 1) - Numbers.n - 1)
FROM Numbers
WHERE
Numbers.n <= LEN(@DelimitedText) - 1 AND
SUBSTRING(@DelimitedText, Numbers.n, 1) = @Delimiter
AND
LEN(SUBSTRING(@DelimitedText, Numbers.n + 1,CHARINDEX(@Delimiter, @DelimitedText, Numbers.n + 1) - Numbers.n - 1)) > 0
OPTION (MAXRECURSION 1000)

More fun with number tables can be found here

The first post!

Really this is just a test post. The intent of this blog is to actually log some of the stuff I do. That is for later reference and to give some structure to gained knowledge and experiences . I will probably mostly post stuff about SQL Server and Borland Delphi ...