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

CREATE TABLE #wrkSchedules (
	ScheduleType char(10) not null, 
	ScheduleID int not null,
	ScheduleSeq int not null, 
	ScheduleLengthMinutes int not null, 
	ScheduleStart int, 
	ScheduleEnd int
	)
ALTER TABLE #wrkSchedules ADD CONSTRAINT ws_pk PRIMARY KEY (ScheduleType, ScheduleSeq)

CREATE TABLE #wrkRequests (
	RequestType char(10) not null, 
	RequestID int not null,
	RequestSeq int not null, 
	RequestLengthMinutes int not null, 
	RequestStart int, 
	RequestEnd int,
	Offset int,
	ScheduleID int
	)
ALTER TABLE #wrkRequests ADD CONSTRAINT wr_pk PRIMARY KEY (RequestType, RequestSeq)

INSERT INTO #wrkSchedules (scheduleType,ScheduleID,ScheduleLengthMinutes,ScheduleSeq)
SELECT scheduleType,ScheduleID,ScheduleLengthMinutes, row_number() over (partition by scheduleType order by ScheduleID) ScheduleSeq
FROM tblSchedules

INSERT INTO #wrkRequests (RequestType,RequestID,RequestLengthMinutes,RequestSeq)
SELECT RequestType,RequestID,RequestLengthMinutes, row_number() over (partition by RequestType order by RequestID) RequestSeq
FROM tblRequests

declare @s int = 0

update #wrkSchedules
set @s = ScheduleEnd = case when ScheduleSeq = 1 then 0 else @s end + ScheduleLengthMinutes,
	ScheduleStart = @s - ScheduleLengthMinutes

select * from #wrkSchedules

update #wrkRequests
set @s = RequestEnd = case when RequestSeq = 1 then 0 else @s end + RequestLengthMinutes,
	RequestStart = @s - RequestLengthMinutes

select * from #wrkRequests

declare @o int = 0

-- Altered to get Maximum ScheduleEnd
update #wrkRequests
set @o = offset = 
	case when RequestStart = 0 then
	isnull((select MAX(ScheduleEnd) - RequestStart from #wrkSchedules w where w.scheduletype = requesttype and w.ScheduleEnd between RequestStart and RequestEnd - 1), 0)
	else
	@o + isnull((select MAX(ScheduleEnd) - RequestStart from #wrkSchedules w where w.scheduletype = requesttype and w.ScheduleEnd between RequestStart + @o and RequestEnd + @o - 1), 0)
	end
	
select * from #wrkRequests

update wr
set wr.ScheduleID = ws.ScheduleID 
from #wrkRequests wr
	inner join #wrkSchedules ws 
		on wr.RequestType = ws.ScheduleType 
			and (wr.requestStart + offset) between ws.ScheduleStart and ws.ScheduleEnd - 1
			and (wr.requestEnd + offset) between ws.ScheduleStart + 1 and ws.ScheduleEnd

select * from #wrkRequests
select * from #wrkSchedules

select 	RequestID,
	 wr.ScheduleID,
	 (RequestStart + offset) - ScheduleStart RequestChunkStartTime
from  #wrkRequests wr
inner join #wrkSchedules ws on wr.ScheduleID = ws.ScheduleID