CREATE TABLE dbo.Holidays (HolidayDate date primary key, HolidayName varchar(30));
WITH Dates (N, CalendarDate) as
(SELECT TOP(40000) N, DATEADD(DAY,N-1,CONVERT(DATE,'1/1/2000')) FROM vTally)
,Expansion1 as -- DATEPARTS
(SELECT N, CalendarDate
,CONVERT(smallint,DATEPART(day,CalendarDate)) as DayNo
,CONVERT(tinyint,DATEPART(week,CalendarDate)) as WeekNo
,CONVERT(tinyint,DATEPART(month,CalendarDate)) as MonthNo
,CONVERT(tinyint,DATEPART(quarter,CalendarDate)) as QuarterNo
,CONVERT(smallint,DATEPART(year,CalendarDate)) as YearNo
,CONVERT(tinyint,DATEPART(DW,CalendarDate)) as [DayofWeek]
,CONVERT(bit, CASE WHEN EOMONTH(CalendarDate) = CalendarDate
THEN 1 ELSE 0 END) as EndOfMonth
,CONVERT(bit, CASE WHEN DATEPART(DW,CalendarDate) IN (1,7)
THEN 1 ELSE 0 END) as Weekend
FROM Dates d)
,Expansion2 as
(SELECT *,CONVERT(bit, CASE WHEN ROW_NUMBER() OVER(PARTITION BY YearNo,MonthNo,[DayofWeek] ORDER BY N DESC) = 1
THEN 1 ELSE 0 END) as LastDoWInMonth
,CONVERT(tinyint, ROW_NUMBER() OVER(PARTITION BY YearNo,MonthNo,[DayofWeek]
ORDER BY N)) as DoWAsc
,CONVERT(tinyint,((DayNo-1)/7)+1) as WeekNoAlt
FROM Expansion1)
INSERT INTO Holidays (HolidayDate, HolidayName)
-- variable-date holidays
SELECT CalendarDate as HolidayDate, HolidayName
FROM Expansion2 x2
CROSS APPLY (VALUES (CASE WHEN MonthNo = 5 and [DayOfWeek] = 2 and LastDoWInMonth = 1 THEN 'Memorial Day'ELSE NULL END)
,(CASE WHEN MonthNo = 9 and [DayofWeek] = 2 and [DoWAsc] = 1 THEN 'Labor Day' ELSE NULL END)
,(CASE WHEN MonthNo = 11 and [DayofWeek] = 5 and [DoWAsc] = 4 THEN 'Thanksgiving Day'ELSE NULL END)
) v (HolidayName)
WHERE HolidayName is not null
AND CalendarDate < '1/1/2100'
UNION ALL
-- Hixed-date Holidays
SELECT TOP(300) DATEADD(YEAR,N-1,CONVERT(date,v.HolidayDate)) as HolidayDate
,HolidayName
FROM dbo.vTally t
CROSS JOIN (VALUES ('1/1/2000','New Years Day')
, ('7/4/2000','Independence Day')
, ('12/25/2000', 'Christmas Day')
) as v (HolidayDate, HolidayName)
ORDER BY CalendarDate
GO
SELECT TOP(20) HolidayDate,HolidayName from Holidays