A SQL Script to generate a 4-4-5 Mon-Sun calendar table

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