5.11 Finding Available Periods

5.11.1 Problem

You want to derive a list of dates that are not marked as reserved in the ContractorsSchedules table. Such a query would be useful for finding days on which there is at least one contractor available. If a client calls and needs a contractor in January or February, the query should list dates during those months when contractors are available.

5.11.2 Solution

The Pivot table is part of the solution to this problem, because we need to generate a complete list of dates in a given period. The complete solution query is as follows:

SELECT  
   CAST(DATEADD(day,i,'2001-1-1') AS CHAR(12)) Date
FROM Pivot, ContractorsSchedules c1
WHERE 
   DATEADD(d,i,'2001-1-1') BETWEEN '2001-1-1' AND '2001-2-28'
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  1 2001 
Jan  2 2001 
Jan  3 2001 
Jan  4 2001 
Jan 16 2001 
Jan 17 2001 
Jan 18 2001 
Jan 19 2001 
Jan 20 2001 
...

5.11.3 Discussion

The query adds the Pivot value (the i column) to the beginning date of our period (2001-1-1) and restricts the results to those dates that fall between 2001-1-1 and 2001-2-28. Note that the Pivot numbers begin with 0. The following expression computes the complete set of dates:

CAST(DATEADD(day,i,'2001-1-1') AS CHAR(12)) Date

and the following condition restricts the list of dates to those falling in January and February:

WHERE 
   DATEADD(d,i,'2001-1-1') BETWEEN '2001-1-1' AND '2001-2-28'

The query then joins each candidate date with rows from the ContractorsSchedules table. Together with the GROUP BY clause, a Cartesian join is created of all booked periods for each candidate date. The actual selection of the result dates is made within the HAVING clause:

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)

The HAVING clause looks complex. It works by querying for the number of contractors booked on the day in question and comparing to see whether that value is less than the total number of contractors. The subquery counts contractors booked on the day in question:

SELECT COUNT(DISTINCT c2.ContractorId) 
FROM ContractorsSchedules c2 
WHERE DATEADD(day,i,'2001-1-1') BETWEEN c2.JobStart AND c2.JobEnd

The second COUNT returns the number of all contractors in the Cartesian product:

COUNT(DISTINCT c1.ContractorId)

The comparison operator checks to see whether all contractors are actually booked on the date in question. If the number of booked periods within one group (representing a candidate date) is equal to the total number of contractors, the check fails and the candidate date is not reported. On the other hand, if not all contractors are booked, there must be some available, so the candidate date is returned in the result set.

Extending the query to report only those dates where there is a team of at least N contractors available is easy. You simply have to add N-1 to the subquery of the counted booked contractors. For example, use the following version of the subquery to return dates on which at least two contractors are available:

SELECT COUNT(DISTINCT c2.ContractorId) + 1
FROM ContractorsSchedules c2 
WHERE DATEADD(day,i,'2001-1-1') BETWEEN c2.JobStart AND c2.JobEnd