[MSSQL] Useful SQL Scripts - CalendarBase

Useful SQL scripts

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
DECLARE @FiscalBeginMonth INT
SELECT @StartDate = DATEADD(MONTH,-1,GETDATE()),@EndDate = GETDATE(),@FiscalBeginMonth = 7
WITH 
    E00(N) AS (SELECT 1 UNION ALL SELECT 1),
    E02(N) AS (SELECT 1 FROM E00 a, E00 b),
    E04(N) AS (SELECT 1 FROM E02 a, E02 b),
    E08(N) AS (SELECT 1 FROM E04 a, E04 b),
    E16(N) AS (SELECT 1 FROM E08 a, E08 b),
    E32(N) AS (SELECT 1 FROM E16 a, E16 b),
cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32),
        
CalendarBase as (
	SELECT
		DateKey = n
		, CalendarDate = DATEADD(day, n - 1, @StartDate )
		, FiscalYearBegin = DATEADD(MONTH, DATEDIFF(MONTH, 0, DATEADD(DAY, n - 1, @StartDate )) - (12 + DATEPART(MONTH, DATEADD(day, n - 1, @StartDate )) - @FiscalBeginMonth) % 12, 0)
	FROM cteTally
	WHERE N <= DATEDIFF(day, @StartDate , @EndDate +1)
)
SELECT		DateKey
			, IsoDate       =	CONVERT(char(8), CalendarDate, 112)
			, CalendarDate
			, CalendarYear  =	YEAR(CalendarDate)
			, CalendarQuarter =	(DATEPART(QUARTER,CalendarDate) )
			, CalendarMonth =	MONTH(CalendarDate)
			, CalendarDay   =	DATEPART(DAY, CalendarDate)
			, DayofWk       =	DATEPART(Dw, CalendarDate)
			, CalendarWeekOfMonth = DATEDIFF(week, DATEADD(day,1, CalendarDate-DAY(CalendarDate) + 1) -1, CalendarDate) +1
			, WeekofYr      =	DATEPART(WEEK, CalendarDate)
			, DayofYr       =	DATEPART(DAYOFYEAR, CalendarDate)
			, NameMonth     =	DATENAME(Month, CalendarDate)
			, NameDay       =	DATENAME (Weekday,CalendarDate )
			, FiscalYear    =	CASE	WHEN	MONTH(CalendarDate)  < @FiscalBeginMonth THEN  
												YEAR(CalendarDate)
										ELSE	YEAR(CalendarDate) + 1 
								END
			, FiscalMonth    =	DATEDIFF( MONTH, FiscalYearBegin, CalendarDate) + 1
			, FiscalWeek     =	DATEDIFF( WEEK, FiscalYearBegin, CalendarDate) + 1
			, FiscalDay      =	DATEDIFF( day, FiscalYearBegin, CalendarDate) + 1
			, FirstDateOfWeek =	DATEADD(D, -DATEPART(Dw, CalendarDate)+2, CalendarDate)
FROM CalendarBase