5.16 Using Calendar Information with Periods5.16.1 ProblemYou want to find dates on which at least one contractor is available; however, you are interested only in work days, not in weekends and holidays. 5.16.2 SolutionUse the query shown earlier for finding available periods, and exclude all Saturdays, Sundays, and holidays from the results: SELECT CAST(DATEADD(day,i,'2001-1-1') AS CHAR(12)) Date FROM Pivot, ContractorsSchedules c1 WHERE DATEADD(day,i,'2001-1-1') BETWEEN '2001-1-1' AND '2001-2-28' AND DATEPART(weekday,DATEADD(day,i,'2001-1-1')) BETWEEN 2 AND 6 AND NOT EXISTS(SELECT * FROM Holidays WHERE holiday=DATEADD(day,i,'2001-1-1')) 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 3 2001 Jan 4 2001 Jan 16 2001 Jan 17 2001 Jan 18 2001 Jan 19 2001 Jan 22 2001 ... Note that January 20 and 21 are not listed in this output, because those dates represent a Saturday and a Sunday. Similarly, January 1 is excluded because it is a holiday. 5.16.3 DiscussionThis query uses the Pivot table to generate all possible dates in the range of interest. The results are joined to the ContractorsSchedules table, so that all possible reserved periods are joined to each date. The WHERE clause conditions function as follows :
After the WHERE clause evaluations take place, each remaining selection of candidate members is grouped via the GROUP BY clause. Each row that is summarized represents one contractor who is available on the date in question. The HAVING clause restricts query results to those dates on which the total number of contractors exceeds the number of assigned contractors. |