Calculate Holidays

CREATE FUNCTION GetHolidays( @Year INT = 0 )
RETURNS @Holidays TABLE(
ItemID INT,
Holiday_Title VARCHAR(50),
Holiday_Date DATETIME
)

AS

BEGIN

DECLARE @Month INT
DECLARE @Day INT
DECLARE @Holiday VARCHAR(50)
DECLARE @HolidayDate DATETIME
DECLARE @TmpDate VARCHAR(10)
DECLARE @ItemCount INT
DECLARE @Now DATETIME

-- Calculate Easter Sunday
-- Alogrithm modeled after VB2TheMax code
DECLARE @g INT
DECLARE @c INT
DECLARE @h INT
DECLARE @i INT
DECLARE @j INT
DECLARE @l INT

SET @g = @Year % 19
SET @c = @Year / 100
SET @h = ((@c - (@c / 4) - ((8 * @c + 13) / 25) + (19 * @g) + 15) % 30)
SET @i = @h - ((@h / 28) * (1 - (@h /28) * (29 / (@h + 1)) * ((21 - @g) / 11)))
SET @j = ((@Year + (@Year / 4) + @i + 2 - @c + (@c / 4)) % 7)
SET @l = @i - @j
SET @Month = 3 + ((@l + 40) / 44)
SET @Day = @l + 28 - (31 * (@Month / 4))
SET @TmpDate = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4))
SET @Holiday = 'Easter Sunday'
SET @HolidayDate = CAST(@TmpDate AS DATETIME)
SET @ItemCount = 1

INSERT INTO @Holidays (ItemID, Holiday_Title, Holiday_Date) VALUES (@ItemCount, @Holiday, @HolidayDate)

-- Calculate Thanksgiving
SET @Holiday = 'Thanksgiving'
SET @Month = 11
SET @ItemCount = @ItemCount + 1

DECLARE @X INT
DECLARE @TCount INT

SET @TCount = 0
SET @X = 1

WHILE @X <= 30
BEGIN
IF DATENAME(DW, CAST(CAST(@Month AS VARCHAR(2)) + '/' + CAST(@X AS VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) AS DATETIME)) = 'Thursday'
BEGIN
SET @TCount = @TCount + 1
IF @TCount = 4
BEGIN
BREAK
END
END
SET @X = @X + 1
END
SET @Day = @X
SET @TmpDate = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4))
SET @HolidayDate = CAST(@TmpDate AS DATETIME)

INSERT INTO @Holidays (ItemID, Holiday_Title, Holiday_Date) VALUES (@ItemCount, @Holiday, @HolidayDate)

-- Calculate Memorial Day
SET @ItemCount = @ItemCount + 1
SET @Month = 5
SET @Holiday = 'Memorial Day'
SET @X = 1

WHILE @X <= 31
BEGIN
IF DATENAME(DW, CAST('5/' + CAST(@X AS VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4)) AS DATETIME)) = 'Monday'
BEGIN
SET @Day = @X
END

IF @X = 31
BEGIN
BREAK
END
SET @X = @X + 1
END

SET @TmpDate = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4))
SET @HolidayDate = CAST(@TmpDate AS DATETIME)

INSERT INTO @Holidays (ItemID, Holiday_Title, Holiday_Date) VALUES (@ItemCount, @Holiday, @HolidayDate)

-- Calculate Labor Day
SET @ItemCount = @ItemCount + 1
SET @Month = 9
SET @Holiday = 'Labor Day'
SET @X = 1

WHILE @X <= 8
BEGIN
IF DATENAME(DW, CAST('9/' + CAST(@X AS VARCHAR(2)) + '/' +  CAST(@Year AS VARCHAR(4)) AS DATETIME)) = 'Monday'
BEGIN
SET @Day = @X
BREAK
END
SET @X = @X + 1
END

SET @TmpDate = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day As VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4))
SET @HolidayDate = CAST(@TmpDate AS DATETIME)

INSERT INTO @Holidays (ItemID, Holiday_Title, Holiday_Date) VALUES (@ItemCount, @Holiday, @HolidayDate)

RETURN

END