Example of calculating holidays vs. business days in a date range (not a question)

[cbarlow3]

Someone asked a question in a previous thread regarding calculating the number of business days between two dates.  I've attached a report that lets you enter a StartDate and EndDate, and outputs all dates in that range.  For each date, it outputs the date, the day of the week, and a "category", where the category is either "Saturday", "Sunday", the name of a holiday (sometimes with "(observed)" if the actual holiday is on a fixed date but falls on a Saturday or Sunday, e.g. since New Year's Day (Jan 1) fell on Sunday in 2012, we in the United States took off Monday Jan 2 instead as a work holiday), or a category of "Working Day".  Naturally, the methods used could be used as part of a calculation (e.g., find all days in a date range that have a category of "Working Day", and then count how many there are).

A few notes about the methods used--first, you must of course decide which holidays you really want to include as non-working days, depending on your locale, etc.  Secondly, my SQL query takes advantage of the WITH clause (also known as common table entries or CTE), and furthermore, it uses recursion to generate all the dates between $P{StartDate} and $P{EndDate}.  If your flavor of SQL doesn't support recursion, you can use a really large table of VALUES (1, 2, 3, 4, ...) up to whatever the max days you'll support as a range between the two dates.  If your version of SQL doesn't support CTE...well, your version will look a lot messier than mine with all the nested queries.

Once you have a temporary table that has each row returning a date in the date range, I am using four techniques to categorize each date:

1. DAYOFWEEK(date):  The version of DAYOFWEEK I'm using returns 1 for Sunday and 7 for Saturday.

2. Fixed date holidays (like Jan 1 New Years, Dec 25 Christmas, etc.).  Obviously those are easy to find using MONTH(date) and DAY(date), although I also check to see if it's a Friday and the day before or Monday and the day after to catch the "observed" holidays.

3. Holidays that move and are of the "Third Monday in January" format.  These are simpler than most people make them out to be.  You can just see if this is January, Monday, and a day from 15 to 21 in this example.

4. Movable feasts.  Ah, "computus".  I'm using essentially a Gaussian formula from Calendrical Calculations by Dershowitz and Reingold to calculate Easter.  Since my report considers Saturday and Sunday to be a higher precedence category than any holiday, I chose to display "Good Friday" (Friday two days before Easter) instead of Easter itself, which still shows up as category "Sunday".  I'm using the standard Western definition of Easter--first Sunday AFTER the Paschal Moon, which is the first (calculated) full moon on or after March 21.  I've also made sure that I only calculate Easter one time for each year in the date range, rather than once for each date in the range that I want to categorize.

Also note that I chose to format my output dates in mm/dd/yyyy format, just because that's what we're most used to in the U.S.  That's just done with the "Pattern" property in iReport and has no bearing on the actual calculation in the SQL query.

I hope that having all this logic in one place will be a useful starting point for many of you  who have to deal with holidays vs. working days without necessarily having to resort to using a limited table of holidays that needs to be refreshed periodically (usually manually).  I've attached the full jrxml, but I've also pasted the main SQL query below.

Carl

Code:

Post Edited by cbarlow3 at 01/31/2012 21:25