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.
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 the 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
RunningDay = 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