Calendar Tables: An Invaluable Database Tool

Submitted by brians on Wed, 03/23/2011 - 19:56

It's Friday, and the boss just asked you to produce a report that summarizes all the issues your team has dealt with in the past year, along with a chart that indicates at a glance the average number of business days that passed between the date an issue was reported to the date it was closed. No problem, right? But how can you reliably count only actual business days without including weekends or holidays? And to make matters worse, your company has a policy of giving employees Fridays off if a holiday falls on a Saturday, and Mondays off if one falls on a Sunday. You also get to take the Friday after Thanksgiving as a holiday. What to do?

This sort of situation is the perfect scenario for a calendar table to swoop in and save the day. I've worked on several applications which made use of a calendar table, and I adapted the concepts at this site and another one to suit my needs.

What is a calendar table? In its most basic form, a calendar table is simply a database table that contains one record for every date in a specified range and a date field to store each individual date. Additional fields can store values to quickly determine the year, month, day, day of the week, day of the year or week number, or to indicate whether a date is a weekend, a holiday, a payday, or whatever characteristic you want to assign to a given day. Thus, because a calendar table can explicitly identify which dates are actual business days, a simple SQL query can easily provide you with the necessary data to satisfy your boss's reporting requirements.

I'm going to explain how to build a calendar table with the MySQL database, but the basic principles should be easily applicable to other databases as well.

The first thing you need to do before building a calendar table is to determine the range of dates you want to store in it and then calculate the total number of records the table will hold. Assuming you've connected to your MySQL server with the mysql client, the following command will produce the number of days between the ending and starting date:

SELECT datediff('2040-12-31','2010-01-01');

This produces a count of 11.322 days.

Now let's create the actual calendar table. We'll need not only dates, but also various fields that can be used to set additional data associated with each date, such as holidays, weekends, paydays, etc.

CREATE TABLE calendar_table (
	dt DATE NOT NULL PRIMARY KEY,
	y SMALLINT NULL,
	q tinyint NULL,
	m tinyint NULL,
	d tinyint NULL,
	dw tinyint NULL,
	monthName VARCHAR(9) NULL,
	dayName VARCHAR(9) NULL,
	w tinyint NULL,
	isWeekday BINARY(1) NULL,
	isHoliday BINARY(1) NULL,
	holidayDescr VARCHAR(32) NULL,
	isPayday BINARY(1) NULL
);

After that's done, we need to populate that new table with one record for each date in the desired range. Rather than write a program to insert all 11,322 records one by one, we can use MySQL to create data right out of thin air! If you need more dates than can be obtained with this code, simply add more joins and calculations to obtain the desired number.

CREATE TABLE ints ( i tinyint );
 
INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 
INSERT INTO calendar_table (dt)
SELECT DATE('2010-01-01') + INTERVAL a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i DAY
FROM ints a JOIN ints b JOIN ints c JOIN ints d JOIN ints e
WHERE (a.i*10000 + b.i*1000 + c.i*100 + d.i*10 + e.i) <= 11322
ORDER BY 1;

Now that the table is populated with dates, we're ready to set the other fields to appropriate values. The following SQL will mark which dates are weekends as well as fill in the year, month, day, day of the week, quarter, month name, day name and week number fields.

UPDATE calendar_table
SET isWeekday = CASE WHEN dayofweek(dt) IN (1,7) THEN 0 ELSE 1 END,
	isHoliday = 0,
	isPayday = 0,
	y = YEAR(dt),
	q = quarter(dt),
	m = MONTH(dt),
	d = dayofmonth(dt),
	dw = dayofweek(dt),
	monthname = monthname(dt),
	dayname = dayname(dt),
	w = week(dt),
	holidayDescr = '';

The following SQL sets the New Year's Day holiday and handles situations where a holiday falls on a weekend.

UPDATE calendar_table SET isHoliday = 1, holidayDescr = 'New Year''s Day' WHERE m = 1 AND d = 1;
 
UPDATE calendar_table c1
LEFT JOIN calendar_table c2 ON c2.dt = c1.dt + INTERVAL 1 DAY
SET c1.isHoliday = 1, c1.holidayDescr = 'Holiday for New Year''s Day'
WHERE c1.dw = 6 AND c2.m = 1 AND c2.dw = 7 AND c2.isHoliday = 1;
 
UPDATE calendar_table c1
LEFT JOIN calendar_table c2 ON c2.dt = c1.dt - INTERVAL 1 DAY
SET c1.isHoliday = 1, c1.holidayDescr = 'Holiday for New Year''s Day'
WHERE c1.dw = 2 AND c2.m = 1 AND c2.dw = 1 AND c2.isHoliday = 1;

Set the Martin Luther King holiday (third Monday in January).

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'Martin Luther King Day'
WHERE m = 1 AND dw = 2 AND d BETWEEN 15 AND 21;

Set the President's Day holiday (third Monday in February).

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'President''s Day'
WHERE m = 2 AND dw = 2 AND d BETWEEN 15 AND 21;

Set the Memorial Day holiday (last Monday in May).

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'Memorial Day'
WHERE m = 5 AND dw = 2 AND d BETWEEN 25 AND 31;

Set the Independence Day holiday and handle situations where a holiday falls on a weekend.

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'Independence Day'
WHERE m = 7 AND d = 4;
 
UPDATE calendar_table c1
LEFT JOIN calendar_table c2 ON c2.dt = c1.dt + INTERVAL 1 DAY
SET c1.isHoliday = 1, c1.holidayDescr = 'Holiday for Independence Day'
WHERE c1.dw = 6 AND c2.m = 7 AND c2.d = 4 AND c2.dw = 7 AND c2.isHoliday = 1;
 
UPDATE calendar_table c1
LEFT JOIN calendar_table c2 ON c2.dt = c1.dt - INTERVAL 1 DAY
SET c1.isHoliday = 1, c1.holidayDescr = 'Holiday for Independence Day'
WHERE c1.dw = 2 AND c2.m = 7 AND c2.d = 4 AND c2.dw = 1 AND c2.isHoliday = 1;

Set the Labor Day holiday (first Monday in September).

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'Labor Day'
WHERE m = 9 AND dw = 2 AND d BETWEEN 1 AND 7;

Set the Veteran's Day holiday and handle situations where the holiday falls on a weekend.

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'Veteran''s Day'
WHERE m = 11 AND d = 11;
 
UPDATE calendar_table c1
LEFT JOIN calendar_table c2 ON c2.dt = c1.dt + INTERVAL 1 DAY
SET c1.isHoliday = 1, c1.holidayDescr = 'Holiday for Veteran''s Day'
WHERE c1.dw = 6 AND c2.m = 11 AND c2.d = 11 AND c2.dw = 7 AND c2.isHoliday = 1;
 
UPDATE calendar_table c1
LEFT JOIN calendar_table c2 ON c2.dt = c1.dt - INTERVAL 1 DAY
SET c1.isHoliday = 1, c1.holidayDescr = 'Holiday for Veteran''s Day'
WHERE c1.dw = 2 AND c2.m = 11 AND c2.d = 11 AND c2.dw = 1 AND c2.isHoliday = 1;

Set the Thanksgiving Day holiday (fourth Thursday in November).

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'Thanksgiving Day'
WHERE m = 11 AND dw = 5 AND d BETWEEN 22 AND 28;

Set a Black Friday holiday if desired (day after Thanksgiving, or the fourth Friday in November).

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'Friday After Thanksgiving'
WHERE m = 11 AND dw = 6 AND d BETWEEN 21 AND 29;

Set the Christmas Day holiday and handle situations where the holiday falls on a weekend.

UPDATE calendar_table
SET isHoliday = 1, holidayDescr = 'Christmas Day'
WHERE m = 12 AND d = 25;
 
UPDATE calendar_table c1
LEFT JOIN calendar_table c2 ON c2.dt = c1.dt + INTERVAL 1 DAY
SET c1.isHoliday = 1, c1.holidayDescr = 'Holiday for Christmas Day'
WHERE c1.dw = 6 AND c2.m = 12 AND c2.d = 25 AND c2.dw = 7 AND c2.isHoliday = 1;
 
UPDATE calendar_table c1
LEFT JOIN calendar_table c2 ON c2.dt = c1.dt - INTERVAL 1 DAY
SET c1.isHoliday = 1, c1.holidayDescr = 'Holiday for Christmas Day'
WHERE c1.dw = 2 AND c2.m = 12 AND c2.d = 25 AND c2.dw = 1 AND c2.isHoliday = 1;

Set paydays. Begin with a specified date, then calculate paydays before and after that date up to the beginning and ending dates of the range specified earlier when we calculated the number of records the calendar table would need to have. In this case, paydays are every other Friday, and the payday we will use to begin the calculations is on 3/25/2011.

UPDATE calendar_table
SET isPayday = 1
WHERE dt IN (
		SELECT DATE('2011-03-25') - INTERVAL (a.i*100 + b.i*10 + c.i) * 2 week
		FROM ints a JOIN ints b JOIN ints c
		WHERE DATE('2011-03-25') - INTERVAL (a.i*100 + b.i*10 + c.i) * 2 week >= DATE('2010-01-01')
	UNION
		SELECT DATE('2011-03-25') + INTERVAL (a.i*100 + b.i*10 + c.i) * 2 week
		FROM ints a JOIN ints b JOIN ints c
		WHERE DATE('2011-03-25') + INTERVAL (a.i*100 + b.i*10 + c.i) * 2 week <= DATE('2040-12-31')
);

We have a problem though. Some of those paydays fall on holidays, so we need to move the actual payday to the day before the holiday. The following SQL will accomplish this.

UPDATE calendar_table
SET isPayday = 1
WHERE dt IN (
	SELECT newDt FROM (
		SELECT dt - INTERVAL 1 DAY AS newDt
		FROM calendar_table
		WHERE isPayday = 1 AND isHoliday = 1 AND dt > '2010-01-01'
	) AS x
);
 
UPDATE calendar_table
SET isPayday = 0
WHERE isPayday = 1 AND isHoliday = 1;

Now our calendar table is complete and ready for use. We can use a SQL command something like the following to obtain the number of actual business days that elapsed between the date an issue was opened to when it was closed. The final result is a report that impresses the boss by delivering statistics with pinpoint accuracy.

SELECT workorder_nbr, descr, open_dt, close_dt,
	(SELECT COUNT(*)
	FROM calendar_table
	WHERE dt > open_dt AND dt <= close_dt AND isWeekday = 1 AND isHoliday = 0
	) AS work_days
FROM workorders;

Tags: