>In the query below, I am hoping to get the ChairID, but instead I keep getting 1. If I don't do the set and just have the select, I get a result set with the chairID (see attached image).
>
>It is my best guess that is giving me my result set count if the subquery, but I don't know why. The Print statement always shows 1
>
>
>
>

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

>
>Thanks for any help.
>Kirk

Kirk,
First simplify your subquery. You have unnecessary OR conditions:

set @AlternateChairToSchedule =
 isnull(
 (select top 1 ch.ChairID
  from @Chairs ch
  where ch.ChairID not in 
  (
  	Select roomid 
    from @Cases c
	where @ScheduledStartDateTime < c.endtime and 
	      @ScheduledEndDateTime > c.begintime
  )
  order by ChairID),0)

Next, your query is a little questionable w/o seeing your data structure and some data really. You are selecting roomId in subquery which do have an overlapping timing with @Scheduled(Start and End)DateTime and then filtering out ChairIDs that do not match to those roomIDs. That really may be what you want and in your data probably there is ChairID=1 where roomID=1 either do not exist or not overlapping.
PS: IMHO it is better to explicitly include 'order by' rather then relying on defaults.
Cetin