Team LiB   Previous Section   Next Section

5.12 Finding Common Available Periods

5.12.1 Problem

Similar to group schedulers, such as MS Exchange, you want to find common availability dates for a group of people. With respect to our example, let's say we want to find days in January and February on which both Alex and Bob are available.

5.12.2 Solution

Using logic similar to that used in the "Finding Available Periods" solution, we use the Pivot table to generate candidate dates. We then add a subquery to see whether both Bob and Alex are available for a given date:

SELECT  
   CAST(DATEADD(day,i,'2001-1-1') AS CHAR(12)) Date
FROM Pivot
WHERE 
   DATEADD(day,i,'2001-1-1') 
      BETWEEN '2001-1-1' AND '2001-2-28' AND
   NOT EXISTS(
      SELECT * FROM ContractorsSchedules 
      WHERE (ContractorId='Alex' OR ContractorId='Bob') AND 
         DATEADD(day,i,'2001-1-1') BETWEEN JobStart AND JobEnd
   )

Date         
------------ 
Jan 31 2001 
Feb 21 2001 
Feb 22 2001 
Feb 23 2001 
Feb 24 2001 
Feb 25 2001 
Feb 26 2001 
Feb 27 2001 
Feb 28 2001

5.12.3 Discussion

The problem of needing to find available periods within known schedules occurs frequently, and the solution is actually quite straightforward. The solution query uses the Pivot table to generate a list of all possible dates within the period of interest (February 1-28, 2001, in this case).

The first part of the WHERE clause limits the dates so that they fall within the given period. The second part of the WHERE clause is a correlated subquery that checks, for each date, to see whether either Alex or Bob are booked. If neither Alex nor Bob are booked, the subquery will return no rows, the NOT EXISTS clause will be satisfied, and the date in question will be added to the query's result set.

    Team LiB   Previous Section   Next Section