# 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

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;```