Section 5.12. Finding Common Available Periods

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.