Hi Kirk,

I just wanted to simplify the logic. I used CTE instead of the subquery. I don't see any changes in your code.

BTW, you don't even need top (1) if you're selecting into a variable, e.g.

select @Chair = chair from ...

will return the first record anyway.



>Naomi...I'm sorry, I don't understand with the cte part I guess. I added the order, but I'm just wanting to return only one record that is why the top 1 was in there, which one didn't make a difference.
>
>

>
>	  set @AlternateChairToSchedule=0
>	  set @AlternateChairToSchedule=
>		isnull((select top 1 ch.ChairID
>	    from @Chairs ch
>	    where ch.ChairID not in 
>	    (Select roomid from @Cases c
>			  where ((@ScheduledStartDateTime > begintime and @ScheduledStartDateTime < c.endtime)
>			   or ( @ScheduledEndDateTime  > c.begintime and @ScheduledEndDateTime < c.endtime)
>			   or ( c.begintime > @ScheduledStartDateTime and c.begintime < @ScheduledEndDateTime)
>			   or ( c.endtime > @ScheduledStartDateTime and c.endtime < @ScheduledEndDateTime )
>			   or ( @ScheduledStartDateTime= c.begintime and @ScheduledEndDateTime = c.endtime)
>		 ))),0)
>		if (@AlternateChairToSchedule>0)
>                              print 'Alternate Chair ID: '+cast(@AlternateChairToSchedule as varchar(5))
>

>
>
>Try
>

>;with cte as (Select roomid from @Cases c
>				  where ((@ScheduledStartDateTime > begintime and @ScheduledStartDateTime < c.endtime)
>				   or ( @ScheduledEndDateTime  > c.begintime and @ScheduledEndDateTime < c.endtime)
>				   or ( c.begintime > @ScheduledStartDateTime and c.begintime < @ScheduledEndDateTime)
>				   or ( c.endtime > @ScheduledStartDateTime and c.endtime < @ScheduledEndDateTime )
>				   or ( @ScheduledStartDateTime= c.begintime and @ScheduledEndDateTime = c.endtime))
>
>select top (1) @AlternateChairToSchedule = ISNULL(ch.ChairID,0) from @Charis ch 
>where not exists (select 1 from cte) ORDER BY ch.ChairID
>