Create Sample Data

-- Suppress data loading messages
SET NOCOUNT ON
 
DECLARE @Schedule table
   (  AppID      int   IDENTITY, 
      AppTeam    varchar(20),
      AppStart   datetime,
      AppFinish  datetime
   )
 
INSERT INTO @Schedule VALUES ( 'Start', NULL, '01/11/2007 09:00' )
INSERT INTO @Schedule VALUES ( 'Smith', '01/11/2007 09:00', '01/11/2007 09:30' )
INSERT INTO @Schedule VALUES ( 'Smith', '01/11/2007 10:00', '01/11/2007 10:15' )
INSERT INTO @Schedule VALUES ( 'Jones', '01/11/2007 11:00', '01/11/2007 12:00' )
INSERT INTO @Schedule VALUES ( 'Williams', '01/11/2007 12:00', '01/11/2007 14:45' )
INSERT INTO @Schedule VALUES ( 'Hsiao', '01/11/2007 15:30', '01/11/2007 16:00' )
INSERT INTO @Schedule VALUES ( 'Lopez', '01/11/2007 16:00', '01/11/2007 17:30' )
INSERT INTO @Schedule VALUES ( 'Green', '01/11/2007 17:30', '01/11/2007 18:30' )
INSERT INTO @Schedule VALUES ( 'Alphonso', '01/11/2007 20:00', '01/11/2007 20:30' )
INSERT INTO @Schedule VALUES ( 'End', '01/11/2007 21:00', NULL )

Return to Top

SQL Server 2005 / SQL Server 2008 Solution

-- Determine the Length of Time Required
 
DECLARE @AppNeed int
SET @AppNeed = 45
 
--Find FIRST Available Time Slot
;WITH CTE
AS (  SELECT 
         *, 
         RowNumber = ROW_NUMBER() OVER( ORDER BY AppStart ASC )
      FROM @MySchedule 
   )
   SELECT FirstApptAvail = min( a.AppFinish )
   FROM CTE a
      INNER JOIN CTE b
         ON a.RowNumber = b.RowNumber - 1
   WHERE datediff( minute, a.AppFinish, b.AppStart) >= @AppNeed

Return to Top

--Find All Available Time Slots
 

;WITH CTE
AS (  SELECT 
         *, 
         RowNumber = ROW_NUMBER() OVER( ORDER BY AppStart ASC )
      FROM @MySchedule 
   )
   SELECT TOP 3 ApptOptions = a.AppFinish
   FROM CTE a
      INNER JOIN CTE b
         ON a.RowNumber = b.RowNumber - 1
   WHERE datediff( minute, a.AppFinish, b.AppStart) >= @AppNeed

Return to Top


Additional Resouces

Having a Calendar table is a very useful utility table that can benefit many data querying situations. For this example, two additional columns (AppStart, AppFinish) can be added to the table to handle situations where business hours are not the same for all days.

Return to Top