Section 5.14. Excluding Nonrecurring Events

5.14 Excluding Nonrecurring Events

5.14.1 Problem

You want to include calendar information in your queries, and you wish to exclude national holidays and other nonworking days. Unlike weekends, which recur on a weekly basis, holidays can be characterized as nonrecurring events.

Some holidays do recur on a yearly basis, even on the same date each year, but it's still convenient to treat them as nonrecurring events.

5.14.2 Solution

To implement support for holidays and other special dates, you have to store them in your database. You'll need a table such as the following:

 CREATE TABLE Holidays(    Holiday DATETIME,     HolidayName CHAR(30)   ) 

After creating the table, populate it with a list of applicable holidays. For recipes in this chapter, we will use the following Slovene holidays:

 SELECT CONVERT(CHAR(10),Holiday,120) Holiday,HolidayName FROM Holidays Holiday    HolidayName                     ---------- ------------------------------  2001-01-01 New Year Holiday               2001-01-02 New Year Holiday               2001-02-08 Slovene Culture Holiday        2001-04-16 Easter Monday                  2001-04-27 Resistance Day                 2001-05-01 Labour Holiday                 2001-05-02 Labour Holiday                 2001-06-25 Day of the Republic            2001-08-15 Assumption of Mary             2001-10-31 Reformation Day                2001-12-25 Christmas                      2001-12-26 Independance Day 

To calculate working days between January 1 and March 1, excluding holidays and weekends, use the following query:

 SELECT      COUNT(*) No_working_days,     DATEDIFF(day,'2001-1-1','2001-3-1') No_days FROM Pivot WHERE     DATEADD(day,i,'2001-1-1') BETWEEN '2001-1-1' AND '2001-3-1' AND     DATEPART(weekday, DATEADD(d,i,'2001-1-1')) BETWEEN 2 AND 6 AND    NOT EXISTS(SELECT * FROM Holidays        WHERE holiday=DATEADD(day,i,'2001-1-1')) No_working_days No_days      --------------- -----------  41              59 

Note that in the table, there are three holidays between the dates specified in the query, January 1, 2001 and March 1, 2001, and that the query returns 41, which is exactly 3 days less than the result we calculated when we didn't use the Holidays table.

5.14.3 Discussion

This solution query is just an extension from the one used in the previous recipe; it differs only in the last part of the WHERE clause where support for holidays is added. The first part of the query generates the candidate dates and excludes all recurring events (weekends). The last part of the WHERE clause is a subquery that further excludes any dates listed in the Holidays table:

 NOT EXISTS(SELECT * FROM Holidays        WHERE holiday=DATEADD(day,i,'2001-1-1')) 

This subquery can be used in any query from which you want to exclude holidays. If you have types of dates other than holidays to exclude, you can easily extend this pattern by creating other date-exclusion tables. For example, you could build a separate table to reflect planned downtime for a factory.

You can easily add columns to the Holidays table if you need to track more information about each holiday. The key problem with this solution is that users need to keep the Holidays table up-to-date with enough holiday information to at least cover any period you anticipate using in a query. If populated just for one year, the results are correct only if all queries include only periods within that year. If you query for data beyond the scope of the rows currently in the Holidays table, you will get results, but those results will not be correct.

As in other code recipes, the DATEADD function is just a tool that generates dates from the Pivot table. We used it here, because we needed to generate a row for each date between our two dates. If you build your queries to run against a table with dates in each record, and for which you do not need to generate missing dates on the fly, simply replace the i column from the Pivot table with your own date column:

 DATEPART(dw,SampleDate) BETWEEN 2 AND 6 AND NOT EXISTS(SELECT * FROM Holidays        WHERE holiday=SampleDate)