SQL Calendar Tables

As promised in my Calendar Dimension article, today I’m providing SQL calendar tables. I’m maintaining these in a GitHub project with my SQL calendar scripts. There’s a set for MS SQL Server and Postgres. You’ll also find the Power Query scripts for my Pivot Calendar in there. This article is about the SQL scripts, though.

The scripts produce two tables–cal_actual and cal_fiscal_445. Particularly for the 445 calendar, you’ll want to look at the options you can set to control

• Start year. If set to 0, the earliest year in cal_actual for which it is possible to calculate your 445 calendar will be used.
• First month of the year. Your fiscal calendar can start in any month.
• First day of the week. Any day can be your first day of week.
• Future years. The script is fast. I usually calculate mine out 50 years.

The scripts are not as refined as I might wish them to be, but they are fully functional and error-free. I will certainly improve them in future. The 445 calendar also doesn’t cover every possible option a company might make for a 445 implementation, but it covers the most common scenarios. Most importantly it demonstrates the logic behind generating a 445 calendar which you can tweak to your organization’s exact requirements.

Working with a 445 Calendar

If you’ve never encountered a 445 Fiscal calendar, you are in for a treat when you do! This would be a great discovery project in conjunction with the Contoso database. I covered setting up a Contoso-MS SQL Server environment in this article, and I covered my Contoso-Postgres Docker image in this one.

What is a 445 Calendar Anyway?

At its most basic, a 445 calendar defines a year as 4 quarters of 3 months in which months 1 and 2 have 4 weeks of 7 days (28 days total) and month 3 has 5 weeks of 7 days (35 days total). The 4-week, 4-week, 5-week pattern is where this calendar gets its name.

28 + 28 + 35 = 91 days per quarter * 4 quarters = 364 days per year

Since the year is exactly divisible by 7, every year starts on the same day of the week. Months too have an exact number of weeks. All this makes period-over-period comparisons a dream. Every year starts on the same weekday. Every month starts on the same weekday. Every month has exactly the same number of Saturdays and Mondays as the year prior. It’s all very clean.

Except…

With a 364-day year, the Fiscal calendar becomes more and more mis-aligned to the actual calendar. Many companies don’t want this. They want all the benefits of the 445 calendar, but they also want a fiscal year that begins roughly at the beginning of some month. With no correction to re-align a 445 calendar to the actual calendar, the company’s fiscal year eventually drifts into a completely different month. Organizations that follow a 445 calendar usually have a rule about how the extra days that accumulate between the fiscal 364-day year and the actual 365- or 366-day year will be handled. One common method is once a full week of drift has occured, add an extra week to the year and get back on track. So depending on when leap year falls, every 6 years or so, the organization has a 53-week, 371-day year that realigns its fiscal calendar with the actual calendar. The question is where to put that 53 week. These scripts allow that week be front- or rear-loaded. Front-loaded weeks are recognized as week 5 of Month 1, making Q1 a 545 quarter. Rear-loaded weeks are recognized as week 6 of Month 12, making Q4 a 456 quarter. There are other possibilities such as spreading the week throughout the year into several 8-day weeks (EEK!). I’m sure there are other creative treatments. A single full week in month 1 or 12 are the only options provided by this script.

• Put Your Tags Here