I never saw this thread until you got an answer...

You'll need three queries in your stored proc:

SELECT COUNT(*)
FROM ROOM
WHERE ROOM_TYPE = :ROOM_TYPE

and

SELECT COUNT(*)
FROM RESERVATION
WHERE ((ARRIVAL_DATE BETWEEN :FROM AND :TO-1)
OR (DEPARTURE_DATE BETWEEN :FROM+1 AND :TO)
OR (ARRIVAL_DATE < :FROM AND DEPARTURE_DATE > :TO))
AND ROOM_TYPE = :ROOM_TYPE

and a similar query for CHECK_IN.

Then take the result of the first query and subtract the other two.

HTH,
Set