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

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 WhatField?

Usually you use TOP 1 clause together with ORDER BY otherwise it doesn't make much sense.