Fiscal/Retail 4-5-4 Calendar Function

,

This function returns a 3 year calendar based on a 4-5-4/5-4-4/4-4-5 calendar, also known as a 52/53 week calendar.  The basis of this calendar function was derived from the NRF retail calendar published at https://nrf.com/resources/4-5-4-calendar and the Wikipedia article published at https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar.

The calendar function requires 2 helper functions.  The helper functions were derived from the documentation provided at https://en.wikipedia.org/wiki/Julian_day.

 CREATE Function dbo.fnGetJDNfromYMD (
        @inputYear int
      , @inputMonth int
      , @inputDay int
        )
Returns Table With schemabinding 
     As 
 Return 
 
/* ===========================================================================================
   Author:      Jeff Williams
   Created:     02/06/2020
   Description: Returns the Julian Day Number (jdn) from the input year, month and day
 
   Called From:
        Procedures, functions and queries
        
   Example Calls:
        Select * From dbo.fnGetJDNfromYMD(1582, 1, 1);
        Select * From dbo.fnGetJDNfromYMD(1753, 1, 1);
        Select * From dbo.fnGetJDNfromYMD(1900, 1, 1);
 
   Revision History	
     Date       Edited By       Change
     ---------- --------------- --------------------------------------------------------------
     02/20/2020 Jeff Williams   Created
   =========================================================================================== */
 
 Select jdn
   From (Values (@inputYear, @inputMonth, @inputDay))                                   As v(y, m, d)
  Cross Apply (Values ((1461 * (y + 4800 + (m - 14) / 12 )) / 4 
                    + (367 * (m - 2 - 12 * ((m - 14) / 12))) / 12 
                    - (3 * ((y + 4900 + (m - 14) / 12) / 100)) / 4 + d - 32075))        As j(jdn);
 CREATE Function dbo.fnGetDateFromJDN (
        @julianDayNumber int
        )
Returns Table With schemabinding 
     As 
 Return 
 
/* ===========================================================================================
   Author:      Jeff Williams
   Created:     02/06/2020
   Description: Returns the date for the given Julian Day Number
 
        The following columns are returned:
 
            y                   year
            m                   month
            d                   day
            OutputDate          date in the 'date' data type
            OutputDateTime      date in the 'datetime' data type
            OutputDateTime2     date in the 'datetime2' data type
 
   Note: this function is only valid for dates following 1753-01-01 when using the datetime
         data type.  For dates prior you cannot reference the datetime data type from the
         calling query.
 
   Called From:
        Procedures, functions and queries
        
   Example Calls:
        Select * From dbo.fnGetDateFromJDN(2361331);
        Select * From dbo.fnGetDateFromJDN(2415021);
        Select y, m, d, OutputDate, OutputDateTime2 From dbo.fnGetDateFromJDN(2298874);
        
   Revision History	
     Date       Edited By       Change
     ---------- --------------- --------------------------------------------------------------
     02/20/2020 Jeff Williams   Created
   =========================================================================================== */
 
 Select y
      , m
      , d
      , OutputDate = datefromparts(y, m, d)
      , OutputDateTime = datetimefromparts(y, m, d, 0, 0, 0, 0)
      , OutputDateTime2 = datetime2fromparts(y, m, d, 0, 0, 0, 0, 7)
   From (Values (@julianDayNumber))                                                     As e1(jdn)
  Cross Apply (Values (jdn + 1401 + ((4 * jdn + 274277) / 146097) * 3 / 4 - 38))        As e2(f)
  Cross Apply (Values (5 * (((4 * f + 3) % 1461) / 4) + 2))                             As e3(h)
  Cross Apply (Values ((h % 153) / 5 + 1, (h / 153 + 2) % 12 + 1))                      As e4(d, m)
  Cross Apply (Values (((4 * f + 3) / 1461) - 4716 + (14 - m) / 12))                    As e5(y);
Create Function dbo.fnFiscalCalendar (
        @startYear int
      , @restated int = 0
        )
Returns Table With schemabinding 
     As 
 Return 

/* ===========================================================================================
   Author:      Jeff Williams
   Created:     02/06/2020
   Description: Returns the Fiscal Calendar for the specified 3 year time period beginning
                at @startYear.  We only return 3 years as that is consistent with how the
                fiscal calendar is published by NRF.
 
        The following columns are returned:
 
            FiscalYear
            Restated                    flag indicating whether or not the calendar has been restated
            DimDateID                   calculated YYYYMMDD integer date
            JulianDayNumber
            FiscalDate                  date using the 'datetime' data type
            OrdinalDay                  day number of the year
            USDayInWeek                 0 = Sunday, ..., 6 = Saturday
            ISODayInWeek                1 = Monday, ..., 7 = Sunday
            FiscalWeek                  the fiscal week number for the year (1 - 52/53)
            FiscalDayInWeek             1 through 7 based on the fiscal start/end days
            FiscalQuarter               the 13 (14 for 53 week years) week quarter
            FiscalWeekInQuarter         the week number for the period (Quarter)
            FiscalMonth454              fiscal month based on 4-5-4 calendar
                                            1 = first 4 weeks, 2 = next 5 weeks, 3 = final 4 weeks
            FiscalWeekInMonth454        the week number in each month on the 4-5-4 calendar
            FiscalMonth544              fiscal month based on 5-4-4 calendar
                                            1 = first 5 weeks, 2 = next 4 weeks, 3 = final 4 weeks
            FiscalWeekInMonth544        the week number in each month on the 5-4-4 calendar
            FiscalMonth445              fiscal month based on 4-4-5 calendar
                                            1 = first 4 weeks, 2 = next 4 weeks, 3 = final 5 weeks
            FiscalWeekInMonth445        the week number in each month on the 4-4-5 calendar
 
        When the calendar is not-restated, any years with 53 weeks will include the 53rd week
        at the end of the last month of the 4th quarter as an additional week in that month.
        For the 4-4-5 calendar, the 4-5-4 calendar is used with the 53rd week added as the 5th
        week in the last month making it a 4-5-5 quarter.
 
        Note: month here is not a calendar month, it is the number of weeks defined for each
        calendar type such that each month will have either 4 or 5 equal weeks all starting
        and ending on the same day of the week (Sunday through Saturday).
 
        For the restated calendar - the weeks are shifted by one so that week 2 becomes week 1
        and week 53 becomes week 52.  This allows like for like comparisons - when comparing
        2016->2017 the non-restated calendar would be used and for 2017->2018 the restated
        calendar would be used.

        This function is based on the NRF 4-5-4 Calendar (https://nrf.com/resources/4-5-4-calendar)
        and determines the start/end of the fiscal year as the closest Saturday/Sunday to the
        end of January.

        Other methods of generating the start/end of the fiscal year can be used.  To do so, modify
        the fsYears CTE to return the appropriate start/end JDN days.  This includes changing
        the start/end day in week - for example, you can set the start/end day to Monday through
        Sunday and the week, month, quarter values will be calculated correctly for that period.

        Example - to calculate the start/end as the last Sunday/Saturday of August:

                      , fsYears (FiscalYear, FiscalStart, FiscalEnd)
                     As (
                 Select @startYear + n
                      , cy.jdn - ((cy.jdn + 1) % 7)
                      , ny.jdn - ((ny.jdn + 1) % 7) - 1
                   From (Values (0), (1), (2))                                                          As y(n)
                  Cross Apply dbo.fnGetJDNfromYMD(@startYear + n, 8, 31)                                As cy
                  Cross Apply dbo.fnGetJDNfromYMD(@startYear + n + 1, 8, 31)                            As ny
                        )

        Example - to calculate the start/end as closest Monday/Sunday to the end of June:

                      , fsYears (FiscalYear, FiscalStart, FiscalEnd)
                     As (
                 Select @startYear + n
                      , cy.jdn + (7 - ((cy.jdn + 1) % 7)) + 1
                      , ny.jdn + (7 - ((ny.jdn + 1) % 7))
                   From (Values (0), (1), (2))                                                          As y(n)
                  Cross Apply dbo.fnGetJDNfromYMD(@startYear + n, 6, 27)                                As cy
                  Cross Apply dbo.fnGetJDNfromYMD(@startYear + n + 1, 6, 27)                            As ny
                        )

        Note: To calculate the 'closest' to the end of a month, we start at 3 days prior to the end of that
              month.  This assures that the 'day' we are looking for will be no more than 3 days prior or 3
              days after the end of the month (7 total days with the 4th day being the end of the month).

   Called From:
        Procedures, functions and queries
        
   Example Calls:
        Select * From dbo.fnFiscalCalendar(2016, 0);
        Select * From dbo.fnFiscalCalendar(2017, 1);
        Select * From dbo.fnFiscalCalendar(2020, 0);
        
   Revision History	
     Date       Edited By       Change
     ---------- --------------- --------------------------------------------------------------
     02/20/2020 Jeff Williams   Created
   =========================================================================================== */

   With t (n)
     As (
 Select t.n 
   From (
 Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
      , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
      , iTally (Number)
     As (
 Select Top (371) 
        cast(row_number() over(Order By @@spid) - 1 As int)
   From t t1, t t2
        )
      , fsYears (FiscalYear, FiscalStart, FiscalEnd)
     As (
 Select @startYear + y.n
      , cy.jdn + (6 - ((cy.jdn + 1) % 7)) + 1
      , ny.jdn + (6 - ((ny.jdn + 1) % 7))
   From (Values (0), (1), (2))                                                                  As y(n)
  Cross Apply dbo.fnGetJDNfromYMD(@startYear + y.n, 1, 28)                                      As cy
  Cross Apply dbo.fnGetJDNfromYMD(@startYear + y.n + 1, 1, 28)                                  As ny
        )
      , jdnDates 
     As (
 Select yr.FiscalYear
      , wk.WeeksInYear
      , FiscalStart = yr.FiscalStart + (rc.Restated * 7)
      , yr.FiscalEnd
      , jd.JulianDayNumber
      , OrdinalDay = jd.JulianDayNumber - yr.FiscalStart + 1 - (rc.Restated * 7)
      , USDayInWeek = (jd.JulianDayNumber + 1) % 7
      , ISODayInWeek = (jd.JulianDayNumber % 7) + 1
      , fw.FiscalWeek
      , FiscalDayInWeek = ((jd.JulianDayNumber - yr.FiscalStart) % 7) + 1
      , FiscalQuarter = ((fw.FiscalWeek - 1) / 13) + 1 - (fw.FiscalWeek / 53)
      , pd.FiscalWeekInQuarter
      , FiscalMonth454 = (pd.FiscalWeekInQuarter / 5) + 1
      , FiscalWeekInMonth454 = (pd.FiscalWeekInQuarter % 5) + cast(pd.FiscalWeekInQuarter / 5 As bit)
      , FiscalMonth544 = (FiscalWeekInQuarter - 2) / 4 + 1 - (FiscalWeekInQuarter / 14)
      , FiscalWeekInMonth544 = (FiscalWeekInQuarter - 2) % 4 + 2 - cast(FiscalWeekInQuarter / 6 As bit) + (FiscalWeekInQuarter / 14 * 4)
      , FiscalMonth445 = ((pd.FiscalWeekInQuarter - 1) / 4) - (pd.FiscalWeekInQuarter / 13) + 1
      , FiscalWeekInMonth445 = ((pd.FiscalWeekInQuarter - 1) % 4) + (pd.FiscalWeekInQuarter / 13 * 4 + 1)
   From iTally                                                                                  As t 
  Cross Apply fsYears                                                                           As yr 
  Cross Apply (Values ((yr.FiscalEnd - yr.FiscalStart) / 7 + 1))                                As wk(WeeksInYear)
  Cross Apply (Values (yr.FiscalStart + t.Number))                                              As jd(JulianDayNumber)
  Cross Apply (Values (@restated & wk.WeeksInYear / 53))                                        As rc(Restated)
  Cross Apply (Values (((jd.JulianDayNumber - yr.FiscalStart) / 7) + 1 - rc.Restated))          As fw(FiscalWeek)
  Cross Apply (Values (((fw.FiscalWeek - 1) % 13 + 1) + (fw.FiscalWeek / 53 * 13)))             As pd(FiscalWeekInQuarter)
        )
 Select jd.FiscalYear
      , Restated = @restated
      , DimDateID = jdn.y * 10000 + jdn.m * 100 + jdn.d
      , jd.JulianDayNumber
      , FiscalDate = jdn.OutputDateTime
      , jd.OrdinalDay
      , jd.USDayInWeek
      , jd.ISODayInWeek
      , jd.FiscalWeek
      , jd.FiscalDayInWeek
      , jd.FiscalQuarter
      , jd.FiscalWeekInQuarter
      , jd.FiscalMonth454
      , jd.FiscalWeekInMonth454
      , jd.FiscalMonth544
      , jd.FiscalWeekInMonth544
      , FiscalMonth445 = iif(e1.Fiscal445NotRestated = 1, jd.FiscalMonth454, jd.FiscalMonth445)
      , FiscalWeekInMonth445 = iif(e1.Fiscal445NotRestated = 1, jd.FiscalWeekInMonth454, jd.FiscalWeekInMonth445)
   From jdnDates                                                                                As jd
  Cross Apply dbo.fnGetDateFromJDN(jd.JulianDayNumber)                                          As jdn
  Cross Apply (Values (jd.FiscalQuarter / 4 & jd.WeeksInYear / 53 & ~@restated))                As e1(Fiscal445NotRestated)
  Where jd.FiscalWeek Between 1 And jd.WeeksInYear;
     Go

Rate

Share

Share

Rate