In a previous life, I did A LOT of BI reporting, and everything we did was based on the company’s 4-4-5 fiscal calendar, and a Mon-Sun work week. Â Adding to the fun was the mutant Julian style date JD Edwards uses internally. Â This system violated every known calendaring convention, but “that’s how it’s set up in JDE”, and if you’re a JD Edwards shop, you know that argument renders all others invalid. Â Oh yeah, the fiscal year started on a different day every year.
To make our lives easier and our apps and reports more performant, we utilized calendar tables. Â Problem is, every couple years, we had to add more dates to the tables. Â When this duty fell onto me, rather than being The Last Person, and spend all day fiddling with Excel and a subsequent import, I spent part of a day writing a simple script that can be run whenever necessary to add more dates.
Because we were integrating information from a number of systems (WMS, TMS, LMS and payroll, notably), all with their own equally mutant calendaring systems, we have a fairly wide table with all kinds of date indicators in it. Â This script is kind of a recursion hell, but it needed to be in order to iterate all of the output columns correctly.
Below are the comments from the SQL file; you can download the entire script from this link:Â 4-4-5 Calendar
This script is meant for a 4-4-5 calendar, Mon-Sun week. Every leap year introduces anÂ extra week, which we add in November.
User Variables =
FiscalCalendarStart = The date on which a fiscal year starts. This is used as the base
date for all calculations
EndOfCalendar = The date on which the calendar should end. This does not have to be
the end of a fiscal year, but if it’s not, you might have to run the script again
to get to the end of teh fiscal year.
RunningDaySeed = Usually 1, this is used to measure the number of days since the
calendar began, often used for depreciation
RunningPeriodSeed = Usually 1, the number of fiscal months since the original calendar began
RunningWeekSeed = The number of fiscal weeks since the original calendar began
FiscalYearSeed = The starting fiscal year
Iteration Variables–don’t mess with these
JdeJulian = the date expressed in JDE’s Julian format
CurrentDate = The calendar date being calculated against
WorkWeekSeed = Fiscal Week
WorkPeriodSeed = Fiscal Month
WorkQuarterSeed = Fiscal Quarter
WeekOfMonth = Rolling week of month
FiscalWeekEnding = Last day of the fiscal week
WorkPeriodSeed = Some legacy thing we must have or the world will end.
But, used to assign where the extra “leap week” goes. Based on the 4-4-5 calendar.
IsLeapYear = 29 days in February extra week in November?
Output Columns (most of these exist to make reports easier)
DateSID = JDE’s Julian Date
CalendarDate = the date, duh
Sysdate = YYYYMMDD, based on calendar date
RunnindDay = the number of days since we installed JDE
WorkPeriod = roughly correlates to the fiscal month
RunningPeriod = the number of fiscal months since we installed JDE
WorkWeek = The fiscal (Mon-Sun) week of the year
RunningWeek = the number of fiscal weeks since we installed JDE
FiscalYear = The numeric fiscal year
FiscalYearLabel = the pretty field used on a report
WorkQuarter = The fiscal quarter
FiscalQuarter = Another representation of the fiscal quaruer
FiscalQuarterLabel = used on reports
FiscalPeriod = YYYY + WorkQuarter + WorkPeriod (zero-padded)
FiscalPeriodLabel = used on reports
FiscalWeek = YYYY + WorkQuarter + WorkPeriod (zero-padded) + week of the fiscal month
FiscalWeekLabel = used on reports
CalendarYear = calendar year, duh
CalendarQuarter = the traditional definition of a quarter
CalendarQuarterLabel = used on reports
CalendarMonths = the traditional calendar month
CalendarMonthLabel = used on some reports
WeekEnding = the last day of the calendar week (Saturday)
FiscalWeekEnding = the last day of the fiscal week (Sunday)
FiscalMonth = Based on the fiscal calendar, relates to the WorkPeriod somehow
FiscalMonthLabel = used on some reports, based on the FiscalMonth