Colin, sorry I don't have time to dig into this more, but see if this sample code I have from one of my SQL Saturday sessions can point you in the right direction. NOTE: proper indexing can make this a LOT faster on large datasets:

-- 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 );

-- Determine the Length of Time Required

DECLARE @ApptNeed int;

SET @ApptNeed = 45;

--SET @ApptNeed = 60; --comment out Lopez and run this one

--Find FIRST Available Time Slot

;WITH CTE

AS ( SELECT

*,

RowNumber = ROW_NUMBER() OVER( ORDER BY AppStart ASC )

FROM @Schedule

)

SELECT FirstApptAvail = min( a.AppFinish )

FROM CTE a

INNER JOIN CTE b --be SURE you cover endpoints on self-joins like this!!

ON a.RowNumber = b.RowNumber - 1

WHERE datediff( minute, a.AppFinish, b.AppStart) >= @ApptNeed;