select XX.XCMS_ID xcms_id 
from xcms XX 
where exists (select 1 from yres where yres_resstatus<>3 and yres_xcms_id=XX.XCMS_ID and yres_exparrtime between :STAYFROMDATE and :STAYTODATE group by yres_xcms_id having count(*) >:NOOFSTAYS)