declare @start_date date, @end_date date = '19091229'
if object_id('tempdb..#DateList') is not null
drop table #DateList
CREATE TABLE #DateList(
Calendar_Date date NULL,
Calendar_Date_String varchar(10) NULL,
Calendar_Day int NULL,
Calendar_Month int NULL,
Calendar_Quarter int NULL,
Calendar_Year int NULL,
Day_Name nvarchar(30) NULL,
Is_Weekday int NOT NULL,
Day_of_Week int NULL,
Day_of_Week_in_Month int NULL,
Day_of_Year int NULL,
Day_of_Week_in_Year int NULL,
Day_of_Quarter int NULL,
Day_of_Week_in_Quarter int NULL,
Month_Name nvarchar(30) NULL,
Week_of_Month int NULL,
Week_of_Quarter int NULL,
Week_of_Year int NULL,
First_Date_of_Week datetime NULL,
Last_Date_of_Week datetime NULL,
First_Date_of_Month datetime NULL,
Last_Date_of_Month datetime NULL,
First_Date_of_Quarter datetime NULL,
Last_Date_of_Quarter datetime NULL,
First_Date_of_Year datetime NULL,
Last_Date_of_Year datetime NULL,
Days_in_Month int NULL,
Is_Leap_Year int NOT NULL
)
create unique clustered index ixDateList on #DateList (calendar_Date, calendar_year)
declare @x int
set @start_date = isnull((select max(calendar_date) from #DateList), try_cast('19091229' as date))
while @start_date <= getdate()
begin
;with DateSeries as (
select try_cast(@start_date as date) as Calendar_Date
union all
select dateadd(day,1,Calendar_Date) from DateSeries
where Calendar_Date <= getdate() and Calendar_Date <= dateadd(day, 30000, @start_date)
)insert into #DateList
(Calendar_Date, Calendar_Date_String, Calendar_Day, Calendar_Month, Calendar_Quarter, Calendar_Year, Day_Name, Is_Weekday, Day_of_Week, Day_of_Week_in_Month, Day_of_Year, Day_of_Week_in_Year, Day_of_Quarter, Day_of_Week_in_Quarter, Month_Name, Week_of_Month, Week_of_Quarter, Week_of_Year, First_Date_of_Week, Last_Date_of_Week, First_Date_of_Month, Last_Date_of_Month, First_Date_of_Quarter, Last_Date_of_Quarter, First_Date_of_Year, Last_Date_of_Year, Days_in_Month, Is_Leap_Year)
select
Calendar_Date, -- Base Date.
Calendar_Date_String = try_convert(varchar(10), Calendar_Date, 112), --- formatted date to YYYYMMDD
Calendar_Day = datepart(day,Calendar_Date), --- 1-31
Calendar_Month = datepart(month,Calendar_Date), --- 1-12
Calendar_Quarter = datepart(quarter, Calendar_Date), --- 1-4, indicates quarter within the current year
Calendar_Year = datepart(year,Calendar_Date), --- YYYY
Day_Name = datename(dw, Calendar_Date), --- Name of the day of the week, Monday - Sunday
Is_Weekday = case when datename(dw, Calendar_Date) in ('Saturday','Sunday') then 0 else 1 end,
Day_of_Week = datepart(weekday,Calendar_Date), --- 1-7; day index; Sunday = 1
Day_of_Week_in_Month = (datepart(day,Calendar_Date) + 6) / 7, --- 1-5; sequential. 1-7 is 1, 8-14 is 2... and so on, resets the following month. the week the day belong to. can be used to identify Nth DayName of the month
Day_of_Year = datepart(dy,Calendar_Date), --- 1-366 day in a year
Day_of_Week_in_Year = (datepart(dy,Calendar_Date) + 6) / 7, --- 1-53 weeks in a year. sequential. 1-7 is first week, 8-14 second week ... can be used to identify Nth DayName of the year
Day_of_Quarter = datediff(day,DATEADD(qq, DATEDIFF(qq, 0, Calendar_Date), 0),Calendar_Date)+1,--- 1-92, indicates the day # in the quarter
Day_of_Week_in_Quarter = (datediff(day,DATEADD(qq, DATEDIFF(qq, 0, Calendar_Date), 0),Calendar_Date)+7)/7,--- 1-13, sequential. 1-7 is 1, 8-14 is 2... and so on, resets the following quarter; indicates for example that it's the Nth DayName of the quarter.
Month_Name = datename(month, calendar_date), --- January-December
Week_of_Month = DATEDIFF(WEEK, DATEADD(WEEK, DATEDIFF(WEEK, 0, DATEADD(MONTH, DATEDIFF(MONTH, 0, calendar_date), 0)), 0), calendar_date ) + 1, --- 1-6, indicates the week number within the current month - 1 = initial value and will increment on first sunday being day one of the week - resets the following month
Week_of_Quarter = DATEDIFF(DAY, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, Calendar_Date), 0), Calendar_Date)/7 + 1, --- 1-14, , indicates the week number within the current quarter - 1 = initial value and will increment on the 8th of the month - resets the following month
Week_of_Year = DATEPART(WEEK, Calendar_Date),-- 1-53, indicates the number of week within the current year. starts with 1, increment on first sunday - using day name - first day is sunday
First_Date_of_Week = dateadd(dd,(datepart(dw,DATEADD(dd,DATEDIFF(dd,0,Calendar_Date),0))-1)*-1,DATEADD(dd,DATEDIFF(dd,0,Calendar_Date),0)), --- first date of the week of the date
Last_Date_of_Week = dateadd(ms,-2,dateadd(dd,8-datepart(dw,DATEADD(dd,DATEDIFF(dd,0,Calendar_Date),0)),DATEADD(dd,DATEDIFF(dd,0,Calendar_Date),0))), --- last date of the week of the date
First_Date_of_Month = DATEADD(mm,DATEDIFF(mm,0,Calendar_Date),0), --- first date of the month of the date
Last_Date_of_Month = DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,Calendar_Date)+1,0))), --- last date of the month of the date
First_Date_of_Quarter = DATEADD(qq, DATEDIFF(qq, 0, Calendar_Date), 0), --- first date of quarter of the date
Last_Date_of_Quarter = DATEADD (DAY, -1, DATEADD(QUARTER, DATEDIFF(QUARTER, 0, Calendar_Date) + 1, 0)), --- last date of quarter of the date
First_Date_of_Year = DATEADD(yy,DATEDIFF(yy,0,Calendar_Date),0), --- first date of the year
Last_Date_of_Year = DATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,Calendar_Date)+1,0))), --- last date of the year
Days_in_Month = day(DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,Calendar_Date)+1,0)))), --- number of days in the month
Is_Leap_Year = case when try_cast(try_cast(year(Calendar_Date) as varchar(4)) + '0229' as datetime) is null then 0 else 1 end
from DateSeries
option (maxrecursion 32767)
set @start_date = isnull((select dateadd(day, 1, max(calendar_date)) from #DateList), try_cast('19091229' as date))
end
if object_id('#my_calendar') is not null
drop table #my_calendar
;with HolidayList as (select
l1.Calendar_date,
Holiday_Name = try_cast(case when holidays.HolidayFlag = 1 then 'New Year''s Day: First Day of the Year'
when holidays.HolidayFlag = 2 then 'Martin Luther King, Jr. Day: 3rd Monday in January, beginning in 1983'
when holidays.HolidayFlag = 3 then 'President''s Day: 3rd Monday in February'
when holidays.HolidayFlag = 4 then 'Memorial Day: Last Monday in May'
when holidays.HolidayFlag = 5 then 'Independence Day (USA): 4th of July'
when holidays.HolidayFlag = 6 then 'Labor Day: 1st Monday in September'
when holidays.HolidayFlag = 7 then 'Columbus Day: 2nd Monday in October'
when holidays.HolidayFlag = 8 then 'Veteran''s Day: 11th of November'
when holidays.HolidayFlag = 9 then 'Thanksgiving: 4th Thursday in November'
when holidays.HolidayFlag = 10 then ' Black Friday: Friday after thanksgiving'
when holidays.HolidayFlag = 11 then 'Christmas: 25th of December'
else NULL
end as varchar(100)),
Is_Business_Day = case when holidays.HolidayFlag > 0 then 'Holiday'
when l1.Day_Name in ('Saturday','Sunday') then 'Weekend'
else 'BusinessDay'
end
from #DateList l1
cross apply(select
HolidayFlag =
--- 'New Year's Day'
case when l1.Month_Name = 'January' and l1.Calendar_Day = 1 then 1
--- Martin Luther King, Jr. Day: 3rd Monday in January, beginning in 1983
when l1.Month_Name = 'January'
and l1.Day_Name = 'Monday'
and l1.Day_of_Week_in_Month = 3
AND l1.Calendar_Year >= 1983 then 2
--- President's Day: 3rd Monday in February
when l1.Month_Name = 'February'
AND l1.Day_Name = 'Monday'
AND l1.Day_of_Week_in_Month = 3 then 3
--- Memorial Day: Last Monday in May
when l1.Calendar_date = (select top 1 l2.Calendar_date
from #DateList l2
where l2.Month_Name = 'May' and l2.Day_name = 'Monday' and Calendar_Year >= 1971
order by l2.Calendar_Date desc) then 4
-- Independence Day (USA): 4th of July
when l1.Month_Name = 'July' and l1.Calendar_Day = 4 then 5
--- Labor Day: 1st Monday in September
when l1.Month_Name = 'September'
and l1.Day_Name = 'Monday'
and l1.Day_of_Week_in_Month = 1 then 6
--- Columbus Day: 2nd Monday in October
when l1.Month_Name = 'October'
and l1.Day_Name = 'Monday'
and l1.Day_of_Week_in_Month = 2 then 7
--- Veteran's Day: 11th of November
when l1.Month_Name = 'November'
and l1.Calendar_Day = 11 then 8
--- Thanksgiving: 4th Thursday in November
when l1.Month_Name = 'November'
and l1.Day_Name = 'Thursday'
and l1.Day_of_Week_in_Month = 4 then 9
--- Black Friday: Friday after thanksgiving
when l1.Month_Name = 'November'
and l1.Day_Name = 'Friday'
and l1.Day_of_Week_in_Month = 4 then 10
--- Christmas: 25th of December
when l1.Month_Name = 'December'
and l1.Calendar_Day = 25 then 11
else 0
end
) holidays
), Calendar as (select
l1.Calendar_Date, l1.Calendar_Date_String, l1.Calendar_Day, l1.Calendar_Month, l1.Calendar_Quarter, l1.Calendar_Year, l1.Day_Name, l1.Is_Weekday, l1.Day_of_Week, l1.Day_of_Week_in_Month, l1.Day_of_Year, l1.Day_of_Week_in_Year, l1.Day_of_Quarter, l1.Day_of_Week_in_Quarter, l1.Month_Name, l1.Week_of_Month, l1.Week_of_Quarter, l1.Week_of_Year, l1.First_Date_of_Week, l1.Last_Date_of_Week, l1.First_Date_of_Month, l1.Last_Date_of_Month, l1.First_Date_of_Quarter, l1.Last_Date_of_Quarter, l1.First_Date_of_Year, l1.Last_Date_of_Year, l1.Days_in_Month, l1.Is_Leap_Year,
h.Holiday_Name, h.Is_Business_Day
from #DateList l1
left join HolidayList h on l1.Calendar_Date = h.Calendar_Date
)select
c1.Calendar_Date, c1.Calendar_Date_String,
c1.Calendar_Day, c1.Calendar_Month, c1.Calendar_Quarter, c1.Calendar_Year,
c1.Day_Name, c1.Is_Weekday,
c1.Day_of_Week, c1.Day_of_Week_in_Month,
c1.Day_of_Year, c1.Day_of_Week_in_Year,
c1.Day_of_Quarter, c1.Day_of_Week_in_Quarter,
c1.Month_Name,
c1.Week_of_Month, c1.Week_of_Quarter, c1.Week_of_Year,
c1.First_Date_of_Week, c1.Last_Date_of_Week,
c1.First_Date_of_Month, c1.Last_Date_of_Month,
c1.First_Date_of_Quarter, c1.Last_Date_of_Quarter,
c1.First_Date_of_Year, c1.Last_Date_of_Year,
c1.Days_in_Month, c1.Is_Leap_Year, c1.Holiday_Name,
c1.Is_Business_Day, p.Previous_Business_Day, n.Next_Business_Day
into #my_calendar
from Calendar c1
outer apply(select top 1 c2.Calendar_Date as Previous_Business_Day
from Calendar c2
where datediff(DAY, c2.Calendar_Date, c1.Calendar_Date) <= 7 and c1.Calendar_Date > c2.Calendar_Date and c2.Is_Business_Day = 'BusinessDay'
order by c2.Calendar_Date desc
) p
outer apply(select top 1 c2.Calendar_Date as Next_Business_Day
from Calendar c2
where datediff(DAY, c1.Calendar_Date, c2.Calendar_Date) <= 7 and c1.Calendar_Date < c2.Calendar_Date and c2.Is_Business_Day = 'BusinessDay'
order by c2.Calendar_Date
) n
where Calendar_Year >= 2020 and Calendar_Date < dateadd(year, 5, getdate())
order by 1
create unique clustered index ix#my_calendar on #my_calendar (calendar_date)
select * from #my_calendar
order by 1
Comment?~~ CK
