Availability Groups with Docker Containers (2020-09-21)

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 Saturday/Sunday 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 + (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 + 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, 1) Where OrdinalDay = 1 Order By FiscalYear;
Select * From dbo.fnFiscalCalendar(2017, 0) Order By OrdinalDay, FiscalYear;
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)
checksum(row_number() over(Order By @@spid))
From t t1, t t2
)
, fsYears (FiscalYear, FiscalStart, FiscalEnd)
As (
Select @startYear + y.n
, cy.jdn + (5 - ((cy.jdn + 1) % 7)) + 1
, ny.jdn + (5 - ((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)
, USDayInWeek = (jd.JulianDayNumber + 1) % 7
, ISODayInWeek = (jd.JulianDayNumber % 7) + 1
, fw.FiscalWeek
, FiscalDayInWeek = ((jd.JulianDayNumber - yr.FiscalStart - 1) % 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 - 1) / 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;