Automated Calendars

Each new year brings new challenges, but one challenge that come back year after year is adding your holidays to your calendar, or worse calendars.

Traditionally this has been a manual tedious process, especially if you have multiple calendars, and Easter is its own special challenge.

From version 9.3 though it has been possible to use WAPL to automate this, all the commands you need have been there since the beginning. The trick is knowing how to string them together to make it all work each year without intervention.

The process is broadly split into 2 chunks, calculating the holidays and then applying the holidays to your calendar(s).

To calculate the holidays the holidays the VARDATE command lets you create a variable for a date based on some rules. You can pick particular dates with a month, or days within a month to cover most holidays.

Let’s start with a simple one, the Fourth of July. The VARDATE rule for that is quite simple –

This statement creates a variable call PH_INDEPENDENCE and sets it to the 4th day of July this year, but really you want to be able to calculate your holidays in advance, so you could add YEAR as an extra keyword to the VARDATE statement. Alternatively you can set the default YEAR for your job, which avoids you having to set the YEAR on every holiday you calculate. Not only that you can specify the year relative to this year.

VARDATE = without a variable name before the = sign is what allows you to set defaults for variable calculation.

So that covers setting holidays for a specific date, but what if that date falls on a weekend? Sometimes you might want to bring the move the date to the preceding Friday, other times you might want to move it to the following Monday. Sometimes you might want to move it to the closest non-weekend date, other times you may leave it where it is.

This is where the RULE keyword comes in. This is a little bit like the FREEDAY rule, but explicitly for Saturdays and Sundays. It’s important to understand this as we cannot use FREEDAYs as we are creating calendars here. The values for RULE are –

• BEFORE – If the date lands on a weekend move it to the preceding FRIDAY
• AFTER – If the date lands on a weekend move it to the following MONDAY
• ON – If the date lands on a weekend leave it where it is. This is the default.
• NEAREST – If the date lands on a SATURDAY move it to the preceding FRIDAY. If it lands on a SUNDAY move it to the following MONDAY

Again you can code this keyword on each VARDATE statement, or you can set the default in the same way you set the YEAR. If most holidays follow the same RULE you can set the default and set it specifically only on holidays that don’t follow the default.

If your holidays move to the nearest day it would look something like this.

We’ve covered specific dates in a month, but what about specific days within a month. For example Martin Luther King day is the third Monday of January. All we do is add the DAY keyword for a specific day, and the ONLY value then counts that day type only, rather than every day that month.

Similarly we can count from the end of the month as well. Use LAST instead of ONLY, so LAST(1) DAY(MON) MONTH(AUG) would give you the last MONDAY in August.

So far so good, but what about consecutive holidays, where 2 days are expected to be together in the calendar. In the UK Christmas day is the 25th of December, Boxing Day is the 26th. Except when one or both of them falls on the weekend. So if the 25th of December falls on a Sunday, it would automatically shift to the 26th, but then this overlaps with Boxing Day, which in reality would need to move to the 27th, but if calculated by ONLY(26) MONTH(DEC) if it lands on a MONDAY it will not move.

We handle this by calculating Christmas Day the normal way and use the Christmas Day variable to set the base for calculating Boxing Day. Note that because we are using the value of the Christmas Day variable we need to turn on variable substitution with the VARSUB statement.

The BASE keyword sets the baseline, and the OFFSET moves it onwards a number of days. So whatever date Christmas Day might get moved to, Boxing Day is calculated initially as the day after, and if that lands on a weekend it too will be moved onwards.

Then the BIG question is what about Easter, which moves astronomically each year. WAPL has a special base date called EASTER that returns the date of Easter Sunday for each year. Which can then be offset to get either Good Friday or Easter Monday.

We could have calculated both of these dates using BASE(EASTER) but adding 3 days to Good Friday is a much simpler calculation than calculating Easter Sunday twice, so uses less CPU cycles.

From what we have seen so far you can probably calculate all of your holidays. In the SEQQWAPL library there are some sample members beginning with EQQDR that have sets of date rules for Italy, Netherlands, UK and the US.

Now we have a set of variables, how do we turn that into calendar updates? The trick is to use the CLSTART batch loader statement with some CLDATE keywords to set the specific dates to be free days for each of the variables.

This needs to use DBMODE(UPDATE) so it adds the new dates to the calendar without losing any existing entries. Also for clarity you might want to also use the CLDAY keywords to make it clear which days of the week are work or free days. This in turn makes it easy to use the same technique to add brand new calendars, because whilst CLDAY is optional for existing calendars, for a new calendar you MUST specify the days of the week.

So the update part of the process would look like this.

Your eventual job will be formed of a section to calculate the variables, followed by a section for each calendar you want to apply holiday dates to.

You can create variables at the top of your job for all of the possible public holidays, and then it is up to you which variables you use in a calendar by calendar basis. Some calendars may only use national holidays, others my use both national and regional holidays. You build each calendar specific to your requirements.

Many companies also have a concept of “Bridge days” which is if a holiday falls on a Tuesday, then it will also make the Monday a freeday. Similarly if it falls on a Thursday it will make the Friday a freeday. This can also be handled by a simple bit of code like this to calculate the Bridge day.

We do this by having 2 variables, one for the actual holiday, another for the bridge day. The first thing we do is set the Bridge day to the actual holiday.

Then we can use the @ function to check of the holiday falls on a Tuesday, set the Bridge variable to the holiday minus 1 day. If it falls on a Thursday set the bridge variable to the holiday plus 1 day.

Then when applying it to the calendar code a pair of CLDATE keywords like this.

You should ALWAYS apply the bridge day ahead of the actual holiday. This is because if there is no bridge day the date of the bridge variable will be the same as the holiday. In that case the bridge day will be overwritten by the following actual holiday.

There are many other things you can do with this kind of technique, for example using the PRSTART and PRDATE batch loader to do similar things to automatically build periods.

Getting this tedious job automated each year should lead to a happier new year.