Given a date, say it falls on Monday, how can I know if it is the third Monday or first Monday of the month? I would like to know this so that I can determine if the date falls in my list of holidays or not. Thanks in advance.
Please allow me to suggest that there is a much easier way. Rather than storing a list of holidays with complicated formulae like first Monday and so on, and then trying to figure out if a given date matches any of the stored holidays, consider building a calendar table.
caldate wkday holiday office_open 2007-04-04 4 NULL 1 2007-04-05 5 NULL 1 2007-04-06 6 Good Friday 0 2007-04-07 7 NULL 0 2007-04-08 1 NULL 0 2007-04-09 2 NULL 0 2007-04-10 3 NULL 1 2007-04-11 4 NULL 1
In this example, Friday April 6, 2007 is a holiday (Good Friday), and the office is not open. The office is also not open on Saturday or Sunday, as usual, but neither is it open on the following Monday.
There are two advantages. Firstly, your SQL will be mean and lean, because all you need to do is join to the calendar table, or do a simple lookup query. The query to find the number of business days between two dates is trivial; just SUM the office_open column. Secondly, you will be able to record special events for your particular office, even if they aren't normal holidays.
How to create this calendar table? Manually. It takes only a few minutes every year to prepare the data. I like to use Excel because it's easy to use Edit>Fill>Series to populate the date column, as well as copy/paste for repeating data like the wkday values. Then you can easily forward the worksheet to HR or Accounting, to have them verify the accuracy of the holidays and office closings for your company before you load the data into the database.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading