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:
DimDateID calculated YYYYMMDD integer date
FiscalYear
Restated flag indicating whether or not the calendar has been restated
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 + (8 - ((cy.jdn + 1) % 7))
, 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, Views, Functions and Queries
Example Calls:
Select * From dbo.fnFiscalCalendar(2016, 0) Where OrdinalDay = 1 Order By FiscalYear;
Select * From dbo.fnFiscalCalendar(2016, 1) Where OrdinalDay = 1 Order By FiscalYear;
Select * From dbo.fnFiscalCalendar(2017, 0) Order By OrdinalDay, FiscalYear;
Select * From dbo.fnFiscalCalendar(2017, 1) Order By OrdinalDay, FiscalYear;
Revision History
Date Edited By Change
---------- --------------- --------------------------------------------------------------
02/20/2020 Jeff Williams Created
09/13/2020 Jeff Williams Modified the fsYears CTE to return actual FiscalStart and
FiscalEnd JDN numbers. Modified the jdnDates CTE to account
for actual start/end JDN numbers and modified the iTally to
start at 0 instead of 1.
09/13/2020 Jeff Williams Added FiscalStart and FiscalEnd dates to final query
=========================================================================================== */
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)
checksum(row_number() over(Order By @@spid) - 1)
From t t1, t t2
)
, fsYears (FiscalYear, FiscalStart, FiscalEnd)
As (
Select @startYear + y.n
, cy.jdn + (7 - ((cy.jdn + 1) % 7))
, 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 - (rc.Restated * 7) + 1
, 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
, FiscalStart = fs.OutputDateTime
, FiscalEnd = fe.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 dbo.fnGetDateFromJDN(jd.FiscalStart) As fs
Cross Apply dbo.fnGetDateFromJDN(jd.FiscalEnd) As fe
Cross Apply (Values (jd.FiscalQuarter / 4 & jd.WeeksInYear / 53 & ~@restated)) As e1(Fiscal445NotRestated)
Where jd.FiscalWeek Between 1 And jd.WeeksInYear; 
