--BigQuery_DimDate With CTE_Params As (Select Cast('1950-01-01' As TimeStamp) As StartDate, 36524 As NoOfDays -- 100 years = 36524 days ) , CTE_NumArray As (Select Generate_Array(0, NoOfDays + 7) As Num_Array From CTE_Params) , CTE_SeqNumbers As (Select RecCounter From CTE_NumArray, UnNest(CTE_NumArray.Num_Array) As RecCounter --We will get an extra 7 days to play with ) , CTE_JustDates As (Select Date_Add(Date_Add(StartDate, Interval -7 Day), Interval RecCounter Day) 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(Format_Date('%Y%m%d', Cal_DT) As Int) As YYYYMMDD , Cal_DT , Cast(Format_Date('%d', Cal_DT) As Int) As DayOfM , Cast(Format_Date('%u', Cal_DT) As Int) As DayOfW , Cast(Format_Date('%j', Cal_DT) As Int) As DayOfY , Cast(Format_Date('%V', Cal_DT) As Int) As WeekNo , Cast(Format_Date('%m', Cal_DT) As Int) As MonthNo , Cast(Format_Date('%Q', Cal_DT) As Int) As QuarterNo , Cast(Format_Date('%Y', Cal_DT) As Int) As CalYear , Format_Date('%A', Cal_DT) As Day_Text , Format_Date('%B', Cal_DT) As Month_Text , Format_Date('Q%Q', Cal_DT) As Quarter_Text , Format_Date('%a', Cal_DT) As Day_Text_Abrv , Format_Date('%b', Cal_DT) As Month_Text_Abrv , Format_Date('%YQ%Q', Cal_DT) As YYYYQQ , Cast(Format_Date('%Y%m', Cal_DT) As Int) As YYYYMM , Cast(Format_Date('%Y%V', Cal_DT) As Int) As YYYYWW , Format_Date('%B %Y', Cal_DT) As Rpt_Hdr_Long , Format_Date('%b %Y', Cal_DT) As Rpt_Hdr_Abrv From CTE_JustDates ) , CTE_FixYYYYWWOverYearEnds 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 , (Case When WeekNo > 50 And MonthNo = 1 Then (CalYear - 1) * 100 + WeekNo When WeekNo = 1 And MonthNo = 12 Then (CalYear + 1) * 100 + WeekNo Else YYYYWW End) As YYYYWW , (Case When DayOfW > 5 then 0 Else 1 End) As Is_WeekDay , Rpt_Hdr_Long, Rpt_Hdr_Abrv From CTE_FirstPass) , 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(Format_Date('%Y%m%d', Date_Add(Cal_DT, Interval 0 - DayOfW + 1 Day)) As Int) As CalIFDW , Cast(Format_Date('%Y%m%d', Date_Add(Cal_DT, Interval 0 - DayOfW + 7 Day)) As Int) As CalILDW --DifferentWeekStarts , Min(Cast(Date_Add(Cal_DT, Interval 0 - DayOfW + 2 Day) As Date)) Over (Order by YYYYMMDD Rows Between 1 Preceding And Current Row) As CalDFDW_1 , Min(Cast(Date_Add(Cal_DT, Interval 0 - DayOfW + 3 Day) As Date)) Over (Order by YYYYMMDD Rows Between 2 Preceding And Current Row) As CalDFDW_2 , Min(Cast(Date_Add(Cal_DT, Interval 0 - DayOfW + 4 Day) As Date)) Over (Order by YYYYMMDD Rows Between 3 Preceding And Current Row) As CalDFDW_3 , Min(Cast(Date_Add(Cal_DT, Interval 0 - DayOfW + 5 Day) As Date)) Over (Order by YYYYMMDD Rows Between 4 Preceding And Current Row) As CalDFDW_4 , Min(Cast(Date_Add(Cal_DT, Interval 0 - DayOfW + 6 Day) As Date)) Over (Order by YYYYMMDD Rows Between 5 Preceding And Current Row) As CalDFDW_5 , Min(Cast(Date_Add(Cal_DT, Interval 0 - DayOfW + 7 Day) As Date)) Over (Order by YYYYMMDD Rows Between 6 Preceding And Current Row) As CalDFDW_6 From CTE_FixYYYYWWOverYearEnds ) , CTE_NearlyDone As (Select * , Cast(Concat(Left(Cast(IFDQ As String), 4), '-', Substring(Cast(IFDQ As String), 5, 2), '-', Right(Cast(IFDQ As String), 2)) As Date) As DFDQ , Cast(Concat(Left(Cast(IFDM As String), 4), '-', Substring(Cast(IFDM As String), 5, 2), '-', Right(Cast(IFDM As String), 2)) As Date) As DFDM , Cast(Concat(Left(Cast(IFDW As String), 4), '-', Substring(Cast(IFDW As String), 5, 2), '-', Right(Cast(IFDW As String), 2)) As Date) As DFDW , Cast(Concat(Left(Cast(ILDQ As String), 4), '-', Substring(Cast(ILDQ As String), 5, 2), '-', Right(Cast(ILDQ As String), 2)) As Date) As DLDQ , Cast(Concat(Left(Cast(ILDM As String), 4), '-', Substring(Cast(ILDM As String), 5, 2), '-', Right(Cast(ILDM As String), 2)) As Date) As DLDM , Cast(Concat(Left(Cast(ILDW As String), 4), '-', Substring(Cast(ILDW As String), 5, 2), '-', Right(Cast(ILDW As String), 2)) As Date) As DLDW , Cast(Concat(Left(Cast(CalIFDW As String), 4), '-', Substring(Cast(CalIFDW As String), 5, 2), '-', Right(Cast(CalIFDW As String), 2)) As Date) As CalDFDW , Cast(Format_Date('%Y%m%d', CalDFDW_1) As Int) As CalIFDW_1 , Cast(Format_Date('%Y%m%d', CalDFDW_2) As Int) As CalIFDW_2 , Cast(Format_Date('%Y%m%d', CalDFDW_3) As Int) As CalIFDW_3 , Cast(Format_Date('%Y%m%d', CalDFDW_4) As Int) As CalIFDW_4 , Cast(Format_Date('%Y%m%d', CalDFDW_5) As Int) As CalIFDW_5 , Cast(Format_Date('%Y%m%d', CalDFDW_6) 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 ) , CTE_ViewParams As (Select T.Today , Date_Sub(T.Today, Interval T.GoodDayOffset2 Day) As Today_2 , Date_Sub(T.Today, Interval T.GoodDayOffset3 Day) As Today_3 , T.GoodDayOffset2 , T.GoodDayOffset3 From (Select Cast(Current_Date As Date) As Today --Cast('2024-Jul-02' As Date) 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 = Cast(DD2.Cal_DT As Date) Then DD2.SeqW Else DD2.SeqW - 1 End) As SeqW2, (Case When Date_Add(DD2.CalDFDW_1, Interval 6 Day) = Cast(DD2.Cal_DT As Date) Then DD2.SeqW_1 Else DD2.SeqW_1 - 1 End) As SeqW12, (Case When Date_Add(DD2.CalDFDW_2, Interval 6 Day) = Cast(DD2.Cal_DT As Date) Then DD2.SeqW_2 Else DD2.SeqW_2 - 1 End) As SeqW22, (Case When Date_Add(DD2.CalDFDW_3, Interval 6 Day) = Cast(DD2.Cal_DT As Date) Then DD2.SeqW_3 Else DD2.SeqW_3 - 1 End) As SeqW32, (Case When Date_Add(DD2.CalDFDW_4, Interval 6 Day) = Cast(DD2.Cal_DT As Date) Then DD2.SeqW_4 Else DD2.SeqW_4 - 1 End) As SeqW42, (Case When Date_Add(DD2.CalDFDW_5, Interval 6 Day) = Cast(DD2.Cal_DT As Date) Then DD2.SeqW_5 Else DD2.SeqW_5 - 1 End) As SeqW52, (Case When Date_Add(DD2.CalDFDW_6, Interval 6 Day) = Cast(DD2.Cal_DT As Date) Then DD2.SeqW_6 Else DD2.SeqW_6 - 1 End) As SeqW62, (Case When DD2.DLDM = Cast(DD2.Cal_DT As Date) Then DD2.SeqM Else DD2.SeqM - 1 End) As SeqM2, DD3.Cal_DT As Cal_DT3, DD3.SeqD As SeqD3, (Case When DD3.DLDW = Cast(DD3.Cal_DT As Date) Then DD3.SeqW Else DD3.SeqW - 1 End) As SeqW3, (Case When Date_Add(DD3.CalDFDW_1, Interval 6 Day) = Cast(DD3.Cal_DT As Date) Then DD3.SeqW_1 Else DD3.SeqW_1 - 1 End) As SeqW13, (Case When Date_Add(DD3.CalDFDW_2, Interval 6 Day) = Cast(DD3.Cal_DT As Date) Then DD3.SeqW_2 Else DD3.SeqW_2 - 1 End) As SeqW23, (Case When Date_Add(DD3.CalDFDW_3, Interval 6 Day) = Cast(DD3.Cal_DT As Date) Then DD3.SeqW_3 Else DD3.SeqW_3 - 1 End) As SeqW33, (Case When Date_Add(DD3.CalDFDW_4, Interval 6 Day) = Cast(DD3.Cal_DT As Date) Then DD3.SeqW_4 Else DD3.SeqW_4 - 1 End) As SeqW43, (Case When Date_Add(DD3.CalDFDW_5, Interval 6 Day) = Cast(DD3.Cal_DT As Date) Then DD3.SeqW_5 Else DD3.SeqW_5 - 1 End) As SeqW53, (Case When Date_Add(DD3.CalDFDW_6, Interval 6 Day) = Cast(DD3.Cal_DT As Date) Then DD3.SeqW_6 Else DD3.SeqW_6 - 1 End) As SeqW63, (Case When DD3.DLDM = Cast(DD3.Cal_DT As Date) Then DD3.SeqM Else DD3.SeqM - 1 End) As SeqM3 From CTE_ShouldIdeallyBeStoredAsDimDate As DD Inner Join CTE_ViewParams As C On Cast(DD.Cal_DT As Date) = C.Today Inner Join CTE_ShouldIdeallyBeStoredAsDimDate As DD2 On Cast(DD2.Cal_DT As Date) = C.Today_2 Inner Join CTE_ShouldIdeallyBeStoredAsDimDate As DD3 On Cast(DD3.Cal_DT As Date) = 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