--SQLServer_DimDate
	With CTE_Params As
		(Select Cast('1950-01-01' As DateTime) As StartDate, 36524 As NoOfDays -- 100 years = 36524 days
		)
	, CTE_SeqNumbers(RecCounter) As
		(Select 0 As RecCounter
		Union All
		Select RecCounter + 1
		From CTE_SeqNumbers
		Cross Join CTE_Params
		Where RecCounter < NoOfDays + 7  --We will get an extra 7 days to play with
		)
	, CTE_JustDates As 
		(Select DateAdd(D, RecCounter, DateAdd(D, -7, StartDate)) As Cal_DT  --dates start from 7 days before the StartDate, so that Week calculations start properly from the Startdate.  We drop the extra 7 days later.
		From CTE_SeqNumbers
		Cross Join CTE_Params
		)
	, CTE_FirstPass As
		(Select Cast(Convert(VarChar(50), Cal_DT, 112) As Int) As YYYYMMDD 
			, Cal_DT
			, DatePart(D, Cal_DT) As DayOfM
			, DatePart(DW, Cal_DT) As DayOfW
			, DatePart(DY, Cal_DT) As DayOfY
			, DatePart(WK, Cal_DT) As WeekNo
			, DatePart(M, Cal_DT) As MonthNo
			, DatePart(Q, Cal_DT) As QuarterNo
			, DatePart(YYYY, Cal_DT) As CalYear
			, DateName(DW, Cal_DT) As Day_Text
			, DateName(M, Cal_DT) As Month_Text
			, 'Q' + Convert(Char(1), DateName(QQ, Cal_DT)) As Quarter_Text
			, SubString(DateName(DW, Cal_DT), 1, 3) As Day_Text_Abrv
			, SubString(DateName(M, Cal_DT), 1, 3) As Month_Text_Abrv
			, Convert(VarChar(10), Convert(VarChar(4), DatePart(YYYY, Cal_DT), (0)) + 'Q' + Convert(VarChar(1), DatePart(Q, Cal_DT), (0))) As YYYYQQ
			, DatePart(YYYY, Cal_DT) * 100 + DatePart(MM, Cal_DT) As YYYYMM
			, DatePart(YYYY, Cal_DT) * 100 + DatePart(WK, Cal_DT) As YYYYWW
			, (Case When DatePart(DW, Cal_DT) > 5 Then 0
					Else 1
				End) As Is_WeekDay
			, Convert(VarChar(25), ((DateName(M, Cal_DT) + ' ') + Convert(VarChar(4), DatePart(YYYY, Cal_DT), (0)))) As Rpt_Hdr_Long
			, Convert(VarChar(25), ((SubString(DateName(M, Cal_DT), 1, 3) + ' ') + Convert(VarChar(4), DatePart(YYYY, Cal_DT), (0)))) As Rpt_Hdr_Abrv
		From CTE_JustDates
		)
	, CTE_FirstAndLastDays As
		(Select *
			, Max(DayOfM) Over (Partition By YYYYMM) As DaysInMonth
			, Min(YYYYMMDD) Over (Partition by YYYYWW) As IFDW
			, Min(YYYYMMDD) Over (Partition by YYYYMM) As IFDM
			, Min(YYYYMMDD) Over (Partition by YYYYQQ) As IFDQ
			, Min(YYYYMMDD) Over (Partition by CalYear) As IFDY
			, Max(YYYYMMDD) Over (Partition by YYYYWW) As ILDW
			, Max(YYYYMMDD) Over (Partition by YYYYMM) As ILDM
			, Max(YYYYMMDD) Over (Partition by YYYYQQ) As ILDQ
			, Max(YYYYMMDD) Over (Partition by CalYear) As ILDY
			, Cast(Convert(VarChar(50), DateAdd(D, 0 - DayOfW  + 1, Cal_DT), 112) As Int) As CalIFDW
			, Cast(Convert(VarChar(50), DateAdd(D, 0 - DayOfW  + 7, Cal_DT), 112) As Int) As CalILDW
--DifferentWeekStarts
			, Min(Cast(DateAdd(D, 0 - DayOfW  + 2, Cal_DT) As Date)) Over (Order by YYYYMMDD Rows Between 1 Preceding And Current Row) As CalDFDW_1
			, Min(Cast(DateAdd(D, 0 - DayOfW  + 3, Cal_DT) As Date)) Over (Order by YYYYMMDD Rows Between 2 Preceding And Current Row) As CalDFDW_2
			, Min(Cast(DateAdd(D, 0 - DayOfW  + 4, Cal_DT) As Date)) Over (Order by YYYYMMDD Rows Between 3 Preceding And Current Row) As CalDFDW_3
			, Min(Cast(DateAdd(D, 0 - DayOfW  + 5, Cal_DT) As Date)) Over (Order by YYYYMMDD Rows Between 4 Preceding And Current Row) As CalDFDW_4
			, Min(Cast(DateAdd(D, 0 - DayOfW  + 6, Cal_DT) As Date)) Over (Order by YYYYMMDD Rows Between 5 Preceding And Current Row) As CalDFDW_5
			, Min(Cast(DateAdd(D, 0 - DayOfW  + 7, Cal_DT) As Date)) Over (Order by YYYYMMDD Rows Between 6 Preceding And Current Row) As CalDFDW_6
		From CTE_FirstPass
		)
	, CTE_NearlyDone As
		(Select *
			, Cast(Concat(Left(Cast(IFDQ As VarChar(10)), 4), '-', Substring(Cast(IFDQ As VarChar(10)), 5, 2), '-', Right(Cast(IFDQ As VarChar(10)), 2)) As Date) As DFDQ
			, Cast(Concat(Left(Cast(IFDM As VarChar(10)), 4), '-', Substring(Cast(IFDM As VarChar(10)), 5, 2), '-', Right(Cast(IFDM As VarChar(10)), 2)) As Date) As DFDM
			, Cast(Concat(Left(Cast(IFDW As VarChar(10)), 4), '-', Substring(Cast(IFDW As VarChar(10)), 5, 2), '-', Right(Cast(IFDW As VarChar(10)), 2)) As Date) As DFDW
			, Cast(Concat(Left(Cast(ILDQ As VarChar(10)), 4), '-', Substring(Cast(ILDQ As VarChar(10)), 5, 2), '-', Right(Cast(ILDQ As VarChar(10)), 2)) As Date) As DLDQ
			, Cast(Concat(Left(Cast(ILDM As VarChar(10)), 4), '-', Substring(Cast(ILDM As VarChar(10)), 5, 2), '-', Right(Cast(ILDM As VarChar(10)), 2)) As Date) As DLDM
			, Cast(Concat(Left(Cast(ILDW As VarChar(10)), 4), '-', Substring(Cast(ILDW As VarChar(10)), 5, 2), '-', Right(Cast(ILDW As VarChar(10)), 2)) As Date) As DLDW
			, Cast(Concat(Left(Cast(CalIFDW As VarChar(10)), 4), '-', Substring(Cast(CalIFDW As VarChar(10)), 5, 2), '-', Right(Cast(CalIFDW As VarChar(10)), 2)) As Date) As CalDFDW
			, Cast(Convert(VarChar(50), CalDFDW_1, 112) As Int) As CalIFDW_1
			, Cast(Convert(VarChar(50), CalDFDW_2, 112) As Int) As CalIFDW_2
			, Cast(Convert(VarChar(50), CalDFDW_3, 112) As Int) As CalIFDW_3
			, Cast(Convert(VarChar(50), CalDFDW_4, 112) As Int) As CalIFDW_4
			, Cast(Convert(VarChar(50), CalDFDW_5, 112) As Int) As CalIFDW_5
			, Cast(Convert(VarChar(50), CalDFDW_6, 112) As Int) As CalIFDW_6
		From CTE_FirstAndLastDays
		Cross Join CTE_Params As P
		Where Cal_DT >= P.StartDate
		)
--Select * From CTE_FirstPass
--Select * From CTE_FirstAndLastDays
--Select * From CTE_NearlyDone
	, CTE_ShouldIdeallyBeStoredAsDimDate As
		(Select YYYYMMDD, Cal_DT, DayOfM, DayOfW, DayOfY, WeekNo, MonthNo, QuarterNo, CalYear, Day_Text, Month_Text, Quarter_Text, Day_Text_Abrv, Month_Text_Abrv, YYYYQQ, YYYYMM, YYYYWW, Is_WeekDay, DaysInMonth
			, Rpt_Hdr_Long, Rpt_Hdr_Abrv, CalIFDW, CalIFDW_1, CalIFDW_2, CalIFDW_3, CalIFDW_4, CalIFDW_5, CalIFDW_6, CalDFDW, CalDFDW_1, CalDFDW_2, CalDFDW_3, CalDFDW_4, CalDFDW_5, CalDFDW_6
			, DFDW, DFDM, DFDQ, DLDW, DLDM, DLDQ
			, Dense_Rank() Over (Order By CalYear) As SeqY
			, Dense_Rank() Over (Order By YYYYQQ) As SeqQ
			, Dense_Rank() Over (Order By YYYYMM) As SeqM
			, Dense_Rank() Over (Order By CalIFDW) As SeqW
			, Dense_Rank() Over (Order By CalIFDW_1) As SeqW_1
			, Dense_Rank() Over (Order By CalIFDW_2) As SeqW_2
			, Dense_Rank() Over (Order By CalIFDW_3) As SeqW_3
			, Dense_Rank() Over (Order By CalIFDW_4) As SeqW_4
			, Dense_Rank() Over (Order By CalIFDW_5) As SeqW_5
			, Dense_Rank() Over (Order By CalIFDW_6) As SeqW_6
			, Rank() Over (Order By YYYYMMDD) As SeqD
		From CTE_NearlyDone
--		Option(MaxRecursion 0)  --Will need this line here if it is to be stored As DimDate
	)
	, CTE_ViewParams As
		(Select T.Today
			, Cast(T.Today - T.GoodDayOffset2 As Date) As Today_2
			, Cast(T.Today - T.GoodDayOffset3 As Date) As Today_3
			, T.GoodDayOffset2
			, T.GoodDayOffset3
		From (Select Cast(Cast(GetDate() As Date) As DateTime) As Today --Cast('2024-Jul-02' As DateTime) As Today
				, 2 As GoodDayOffset2
				, 3 As GoodDayOffset3
			) As T
		)

	, CTE_TodaysSeqVals As
		(Select DD.Cal_DT,
		DD2.Cal_DT As Cal_DT2,
		DD2.SeqD As SeqD2,
		(Case When DD2.DLDW = DD2.Cal_DT Then DD2.SeqW Else DD2.SeqW - 1 End) As SeqW2,
		(Case When DateAdd(D, 6, Cast(DD2.CalDFDW_1 As DateTime)) = DD2.Cal_DT Then DD2.SeqW_1 Else DD2.SeqW_1 - 1 End) As SeqW12,
		(Case When DateAdd(D, 6, Cast(DD2.CalDFDW_2 As DateTime)) = DD2.Cal_DT Then DD2.SeqW_2 Else DD2.SeqW_2 - 1 End) As SeqW22,
		(Case When DateAdd(D, 6, Cast(DD2.CalDFDW_3 As DateTime)) = DD2.Cal_DT Then DD2.SeqW_3 Else DD2.SeqW_3 - 1 End) As SeqW32,
		(Case When DateAdd(D, 6, Cast(DD2.CalDFDW_4 As DateTime)) = DD2.Cal_DT Then DD2.SeqW_4 Else DD2.SeqW_4 - 1 End) As SeqW42,
		(Case When DateAdd(D, 6, Cast(DD2.CalDFDW_5 As DateTime)) = DD2.Cal_DT Then DD2.SeqW_5 Else DD2.SeqW_5 - 1 End) As SeqW52,
		(Case When DateAdd(D, 6, Cast(DD2.CalDFDW_6 As DateTime)) = DD2.Cal_DT Then DD2.SeqW_6 Else DD2.SeqW_6 - 1 End) As SeqW62,
		(Case When DD2.DLDM = DD2.Cal_DT Then DD2.SeqM Else DD2.SeqM - 1 End) As SeqM2,
		DD3.Cal_DT As Cal_DT3,
		DD3.SeqD As SeqD3,
		(Case When DD3.DLDW = DD3.Cal_DT Then DD3.SeqW Else DD3.SeqW - 1 End) As SeqW3,
		(Case When DateAdd(D, 6, Cast(DD3.CalDFDW_1 As DateTime)) = DD3.Cal_DT Then DD3.SeqW_1 Else DD3.SeqW_1 - 1 End) As SeqW13,
		(Case When DateAdd(D, 6, Cast(DD3.CalDFDW_2 As DateTime)) = DD3.Cal_DT Then DD3.SeqW_2 Else DD3.SeqW_2 - 1 End) As SeqW23,
		(Case When DateAdd(D, 6, Cast(DD3.CalDFDW_3 As DateTime)) = DD3.Cal_DT Then DD3.SeqW_3 Else DD3.SeqW_3 - 1 End) As SeqW33,
		(Case When DateAdd(D, 6, Cast(DD3.CalDFDW_4 As DateTime)) = DD3.Cal_DT Then DD3.SeqW_4 Else DD3.SeqW_4 - 1 End) As SeqW43,
		(Case When DateAdd(D, 6, Cast(DD3.CalDFDW_5 As DateTime)) = DD3.Cal_DT Then DD3.SeqW_5 Else DD3.SeqW_5 - 1 End) As SeqW53,
		(Case When DateAdd(D, 6, Cast(DD3.CalDFDW_6 As DateTime)) = DD3.Cal_DT Then DD3.SeqW_6 Else DD3.SeqW_6 - 1 End) As SeqW63,
		(Case When DD3.DLDM = DD3.Cal_DT Then DD3.SeqM Else DD3.SeqM - 1 End) As SeqM3
		From CTE_ShouldIdeallyBeStoredAsDimDate As DD
		Inner Join CTE_ViewParams As C On DD.Cal_DT = C.Today
		Inner Join CTE_ShouldIdeallyBeStoredAsDimDate As DD2 On DD2.Cal_DT = C.Today_2
		Inner Join CTE_ShouldIdeallyBeStoredAsDimDate As DD3 On DD3.Cal_DT = C.Today_3
		)
	Select DD.*
		, CS.SeqD2 - DD.SeqD As RelSeqD2
		, CS.SeqW2 - DD.SeqW As RelSeqW2
		, CS.SeqW12 - DD.SeqW_1 As RelSeqW12
		, CS.SeqW22 - DD.SeqW_2 As RelSeqW22
		, CS.SeqW32 - DD.SeqW_3 As RelSeqW32
		, CS.SeqW42 - DD.SeqW_4 As RelSeqW42
		, CS.SeqW52 - DD.SeqW_5 As RelSeqW52
		, CS.SeqW62 - DD.SeqW_6 As RelSeqW62
		, CS.SeqM2 - DD.SeqM As RelSeqM2
		, CS.SeqD3 - DD.SeqD As RelSeqD3
		, CS.SeqW3 - DD.SeqW As RelSeqW3
		, CS.SeqW13 - DD.SeqW_1 As RelSeqW13
		, CS.SeqW23 - DD.SeqW_2 As RelSeqW23
		, CS.SeqW33 - DD.SeqW_3 As RelSeqW33
		, CS.SeqW43 - DD.SeqW_4 As RelSeqW43
		, CS.SeqW53 - DD.SeqW_5 As RelSeqW53
		, CS.SeqW63 - DD.SeqW_6 As RelSeqW63
		, CS.SeqM3 - DD.SeqM As RelSeqM3
	From CTE_ShouldIdeallyBeStoredAsDimDate As DD
	Cross Join CTE_TodaysSeqVals As CS
	Option(MaxRecursion 0)
;
/*
Select YYYYMMDD, Cal_DT, DayOfM, DayOfW, DayOfY, WeekNo, MonthNo, QuarterNo, CalYear, Day_Text, Month_Text, Quarter_Text, Day_Text_Abrv, Month_Text_Abrv, YYYYQQ, YYYYMM, YYYYWW, Is_WeekDay, DaysInMonth
	, Rpt_Hdr_Long, Rpt_Hdr_Abrv, CalIFDW, CalIFDW_1, CalIFDW_2, CalIFDW_3, CalIFDW_4, CalIFDW_5, CalIFDW_6, CalDFDW, CalDFDW_1, CalDFDW_2, CalDFDW_3, CalDFDW_4, CalDFDW_5, CalDFDW_6
	, DFDW, DFDM, DFDQ, DLDW, DLDM, DLDQ, SeqY, SeqQ, SeqM, SeqW, SeqW_1, SeqW_2, SeqW_3, SeqW_4, SeqW_5, SeqW_6, SeqD
From ResearchReporting_V2.Dimension.DimDate
*/
/*
Select YYYYMMDD, Cal_DT, DayOfM, DayOfW, DayOfY, WeekNo, MonthNo, QuarterNo, CalYear, Day_Text, Month_Text, Quarter_Text, Day_Text_Abrv, Month_Text_Abrv, YYYYQQ, YYYYMM, YYYYWW, Is_WeekDay, DaysInMonth
	, Rpt_Hdr_Long, Rpt_Hdr_Abrv, CalIFDW, CalIFDW_1, CalIFDW_2, CalIFDW_3, CalIFDW_4, CalIFDW_5, CalIFDW_6, CalDFDW, CalDFDW_1, CalDFDW_2, CalDFDW_3, CalDFDW_4, CalDFDW_5, CalDFDW_6
	, DFDW, DFDM, DFDQ, DLDW, DLDM, DLDQ, SeqY, SeqQ, SeqM, SeqW, SeqW_1, SeqW_2, SeqW_3, SeqW_4, SeqW_5, SeqW_6, SeqD
	, RelSeqD2, RelSeqW2, RelSeqW12, RelSeqW22, RelSeqW32, RelSeqW42, RelSeqW52, RelSeqW62, RelSeqM2, RelSeqD3, RelSeqW3, RelSeqW13, RelSeqW23, RelSeqW33, RelSeqW43, RelSeqW53, RelSeqW63, RelSeqM3
From ResearchReporting_V2.Dimension.Date
*/