Calculating Thanksgiving By Using SQL in SQL Server

Happy Thanksgiving, here is some SQL code to calculate the next 11 (including today) Thanksgiving days

CREATE TABLE #NumberPivot (Number INT PRIMARY KEY)

DECLARE @intLoopCounter INT
SELECT @intLoopCounter =0

DECLARE @dtmStardate DATETIME
SELECT @dtmStardate ='2005-11-24 00:00:00.000'

DECLARE @dtmEnddate DATETIME
SELECT @dtmEnddate ='2015-11-30 00:00:00.000'

WHILE @intLoopCounter <(SELECT DATEDIFF(d,@dtmStardate,@dtmEnddate))
BEGIN
INSERT INTO #NumberPivot
VALUES (@intLoopCounter)
--+ 7 will only work if you start from a Thursday, use +1 for other days
SELECT @intLoopCounter = @intLoopCounter +7
END


--One way
SET DATEFIRST 7

SELECT CASE WHEN COUNT(*) <> 5
THEN MAX((DATEADD(d,number,@dtmStardate)))
ELSE MAX((DATEADD(d,number,@dtmStardate))) -7 END AS ThanksGivingDay,
DATEPART(yyyy,DATEADD(d,number,@dtmStardate)) AS [Year]
FROM dbo.#NumberPivot
WHERE DATEPART(mm,DATEADD(d,number,@dtmStardate)) = 11
GROUP BY DATEPART(mm,DATEADD(d,number,@dtmStardate)) ,DATEPART(yyyy,DATEADD(d,number,@dtmStardate))

--And another
SELECT dt AS ThanksGivingDay, YEAR(dt) AS [Year]
FROM
( SELECT DATEADD(d,number,@dtmStardate) AS dt
FROM dbo.#NumberPivot
) X
WHERE MONTH(dt)=11
AND DAY(dt) BETWEEN 22 AND 28
ORDER BY YEAR(dt)

--Enjoy the turkey