5.16 Using Calendar Information with Periods

5.16.1 Problem

You want to find dates on which at least one contractor is available; however, you are interested only in work days, not in weekends and holidays.

5.16.2 Solution

Use the query shown earlier for finding available periods, and exclude all Saturdays, Sundays, and holidays from the results:

SELECT  
   CAST(DATEADD(day,i,'2001-1-1') AS CHAR(12)) Date
FROM Pivot, ContractorsSchedules c1
WHERE 
   DATEADD(day,i,'2001-1-1') BETWEEN '2001-1-1' AND '2001-2-28' AND
   DATEPART(weekday,DATEADD(day,i,'2001-1-1')) BETWEEN 2 AND 6 AND
   NOT EXISTS(SELECT * FROM Holidays 
      WHERE holiday=DATEADD(day,i,'2001-1-1'))
GROUP BY i  
HAVING (
   SELECT COUNT(DISTINCT c2.ContractorId) 
   FROM ContractorsSchedules c2 
   WHERE DATEADD(day,i,'2001-1-1') BETWEEN c2.JobStart AND c2.JobEnd)
   <
   COUNT(DISTINCT c1.ContractorId)

Date         
------------ 
Jan  3 2001 
Jan  4 2001 
Jan 16 2001 
Jan 17 2001 
Jan 18 2001 
Jan 19 2001 
Jan 22 2001
...

Note that January 20 and 21 are not listed in this output, because those dates represent a Saturday and a Sunday. Similarly, January 1 is excluded because it is a holiday.

5.16.3 Discussion

This query uses the Pivot table to generate all possible dates in the range of interest. The results are joined to the ContractorsSchedules table, so that all possible reserved periods are joined to each date. The WHERE clause conditions function as follows:

• Condition 1 of the WHERE clause uses the DATEADD function to restrict the results to only the time period in which we are interested. In this example, that period is January 1, 2001 through February 28, 2001.

• Condition 2 of the WHERE clause uses the DATEPART function to exclude Saturdays and Sundays from the results.

• Condition 3 of the WHERE clause uses a subquery to exclude holidays from the results.

After the WHERE clause evaluations take place, each remaining selection of candidate members is grouped via the GROUP BY clause. Each row that is summarized represents one contractor who is available on the date in question. The HAVING clause restricts query results to those dates on which the total number of contractors exceeds the number of assigned contractors.