Share via

SQL Server: How to Find the First Available Timeslot for Scheduling

  • Article

In a scheduling application, it may be desirable to find the first available schedule time (timeslot) for a new appointment. The new appointment must fit completely between existing appointments -without overlap. As the schedule fills, new entries are assigned to the next first available schedule time (timeslot). Alternatively, if desired, the first n available timeslots will be returned for selection.

In the sample data below, the Schedule table is pre-filled with a Start of Day record, and an End of Day record. Normally, that information would be derived from a JOIN with a Calendar table.

A solution for SQL Server 2005 / SQL Server 2008 is provided below.

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

 

FirstApptAvail
2007-01-11 10:15:00.000

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

AppOptions

2007-01-11 10:15:00.000
2007-01-11 14:45:00.000
2007-01-11 18:30:00.000

Return to Top

Additional Resources

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

See Also