Transact-SQL Cookbook by Ales Spetic

Author:Ales Spetic [Ales Spetic and Jonathan Gennick]
Language: eng
Format: epub
Tags: COMPUTERS / Database Management / General
ISBN: 9780596552039
Publisher: O'Reilly Media
Published: 2009-02-09T05:00:00+00:00

Finding Common Available Periods

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.

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

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.