Section 5.11. Finding Available Periods

   

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 
   


Transact-SQL Cookbook
Transact-SQL Cookbook
ISBN: 1565927567
EAN: 2147483647
Year: 2005
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net