## About me

I am a business intelligence developer working at Bizware since August 2015. In this blog I will present code samples, primarilly in tsql but also findings in all other parts of the Microsoft BI-stack.

Disclaimer: All code should be considered as "as-is", i.e. badly tested and not working until extensive testing have been made by anyone reading the code. :)

Happy coding!

## Posts Tagged ‘tsql’

### Calculate gregorian easter sunday fast with a table valued function in tsql

Saturday, August 16th, 2014

Hi!

I’ve seen some examples floating around the net about calculate the easter sunday day. There is a quite complex algoritm behind this, based on the equinox. I’ve stumbled upon some functions in t-sql where you have a regular scalar function to calculate this, but I didn’t find any table valued function, which is a better way when you want to create date dimensions and to check bank holidays. I used sqlsunday.com blog post about easter sunday as a starting point.

So, without further introduction, I present to you a way to get the date from a table valued function instead of a scalar function:

```CREATE FUNCTION [dbo].[ufn_inltab_getGregorianEasterSunday](@year SMALLINT)
RETURNS TABLE
RETURN

WITH
Step1 AS( SELECT a = @year%19, b = FLOOR(1.0*@year/100), c = @year%100 ),
Step2 AS( SELECT a,b,c,d = FLOOR(1.0*b/4), e=b%4, f=FLOOR((8.0+b)/25) FROM Step1 ),
Step3 AS( SELECT a,b,c,d,e,g = FLOOR((1.0+ b- f)/3) FROM Step2 ),
Step4 AS( SELECT a,d,e,h = (19*a+b-d-g+15)%30, i= FLOOR(1.0*c/4), k= @year%4 FROM Step3    ),
Step5 AS( SELECT a,h,l = (32.0+2*e+2*i-h-k)%7 FROM Step4 ),
Step6 AS( SELECT h,l,m = FLOOR((1.0*a+11*h+22*l)/451) FROM Step5 ),
Step7 AS( SELECT easterSunday =
CAST( DATEADD(dd, (h+l-7*m+114)%31,
DATEADD(mm, FLOOR((1.0*h+l-7*m+114)/31)-1,
DATEADD(yy, @year-2000, '2000-01-01') )
) AS DATE)
FROM Step6
)
SELECT easterSunday
FROM Step7

GO```

This function could then be used as a cross apply join to get the yearly gregorian easter sunday. The next snippet shows a test sample for every year since 1950 to 2050:

```-- ================================== Year test ============================ --
-- N-table on the fly:
WITH c1(N) AS ( SELECT N FROM  ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) AS N(N) )
,c2(N) AS (SELECT a.N FROM c1 a CROSS APPLY c1 b)
,c3(N) AS (SELECT a.N FROM c2 a CROSS APPLY c2 b)
,c4(N) AS (SELECT a.N FROM c3 a CROSS APPLY c3 b)

SELECT
YearTab.YearTest
,Easter.easterSunday
FROM
(
SELECT TOP (100)
YearTest = CAST( (ROW_NUMBER() OVER (ORDER BY ( SELECT NULL) ) -1) + 1950 AS SMALLINT)
FROM c4
) YearTab
CROSS APPLY [dbo].[ufn_inltab_getGregorianEasterSunday] ( YearTest ) Easter
```

If you want to create a date dimension table with the years gregorian easter sunday, this might come in handy:

```-- ================================== Day test ============================ --
-- N-table on the fly:
WITH c1(N) AS ( SELECT N FROM  ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) AS N(N) )
,c2(N) AS (SELECT a.N FROM c1 a CROSS APPLY c1 b)
,c3(N) AS (SELECT a.N FROM c2 a CROSS APPLY c2 b)
,c4(N) AS (SELECT a.N FROM c3 a CROSS APPLY c3 b)

SELECT
DayTab.CalenderDate
,DayTab.YearTest
,Easter.easterSunday
FROM
(
SELECT TOP (10000)
CalenderDate = DATEADD(D, ROW_NUMBER() OVER (ORDER BY ( SELECT NULL) ), '2010-01-01' )
,YearTest = YEAR( DATEADD(D, ROW_NUMBER() OVER (ORDER BY ( SELECT NULL) ), '2010-01-01' ) )
FROM c4
) DayTab
CROSS APPLY [dbo].[ufn_inltab_getGregorianEasterSunday] ( YearTest ) Easter
```

I also made a variation of the function to pinpoint if a certain date is the gregorian easter date:

```CREATE FUNCTION [dbo].[ufn_inltab_findGregorianEasterSunday](@date date)
RETURNS TABLE
RETURN

WITH
Step1 AS( SELECT a = YEAR(@date)%19, b = FLOOR(1.0*YEAR(@date)/100), c = YEAR(@date)%100 ),
Step2 AS( SELECT a,b,c,d = FLOOR(1.0*b/4), e=b%4, f=FLOOR((8.0+b)/25) FROM Step1 ),
Step3 AS( SELECT a,b,c,d,e,g = FLOOR((1.0+ b- f)/3) FROM Step2 ),
Step4 AS( SELECT a,d,e,h = (19*a+b-d-g+15)%30, i= FLOOR(1.0*c/4), k= YEAR(@date)%4 FROM Step3    ),
Step5 AS( SELECT a,h,l = (32.0+2*e+2*i-h-k)%7 FROM Step4 ),
Step6 AS( SELECT h,l,m = FLOOR((1.0*a+11*h+22*l)/451) FROM Step5 ),
Step7 AS( SELECT easterSunday =
CAST( DATEADD(dd, (h+l-7*m+114)%31,
DATEADD(mm, FLOOR((1.0*h+l-7*m+114)/31)-1,
DATEADD(yy, YEAR(@date)-2000, '2000-01-01') )
) AS DATE)
FROM Step6
),
Step8 AS ( SELECT isEasterSunday = CASE WHEN easterSunday = @date THEN 1 ELSE 0 END FROM Step7 )
SELECT isEasterSunday
FROM Step8

GO```

This will give you a zero in return for every date that isn’t a gregorian easter sunday. I have only concluded march and april here, for easier visualisation:

```-- ================================== find day test ============================ --
-- N-table on the fly:
WITH c1(N) AS ( SELECT N FROM  ( VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10) ) AS N(N) )
,c2(N) AS (SELECT a.N FROM c1 a CROSS APPLY c1 b)
,c3(N) AS (SELECT a.N FROM c2 a CROSS APPLY c2 b)
,c4(N) AS (SELECT a.N FROM c3 a CROSS APPLY c3 b)

SELECT
DayTab.CalenderDate
,Easter.isEasterSunday
FROM
(
SELECT TOP (10000)
CalenderDate = DATEADD(D, ROW_NUMBER() OVER (ORDER BY ( SELECT NULL) ), '2010-01-01' )
FROM c4
) DayTab
CROSS APPLY [dbo].[ufn_inltab_findGregorianEasterSunday] ( CalenderDate ) Easter
WHERE MONTH(DayTab.CalenderDate) IN (3,4)```

Well, that’s it for today. I hope you will find it usable. I make no guarantees that this code will be correct, use it with your own precaution and test it well!

/Robin