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.
|