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

*/