CREATE PROCEDURE dbo.OnlineRoomsOutOfOrder @AHotelNumber INT,  @ARoomNumberString VARCHAR(50) , @AStart DateTime, @AEnd DateTime AS
SET NOCOUNT ON
/*
    Created by Troels Buch Juni 2007

    Changes: N/A
  

    NOTES: 	

*/

/*DECLARE @RoomNumberID CHAR(50)
SET @RoomNumberID = ''

SELECT RoomNumber FROM Rooms (NOLOCK) 
WHERE Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberString))
AND HotelNumber = 2

SELECT * FROM reservationroomline(NOLOCK) 
inner join reservationroomlinextab(NOLOCK) ON reservationroomline.linenumber=reservationroomlinextab.linenumber 
AND referencenumber=0 
AND roomnumber=145
AND convert(datetime, arrivaldate, 112)+convert(datetime, arrivaltime, 108) < @AEnd
AND convert(datetime, departuredate, 112)+convert(datetime, departuretime, 108) >= @AStart*/

SELECT reservationroomline.RoomNumber AS RoomNumberId, ArrivalDate, ArrivalTime, DepartureDate, DepartureTime, Rooms.Number FROM reservationroomline WITH (NOLOCK) 
INNER JOIN reservationroomlinextab WITH (NOLOCK) ON reservationroomline.linenumber=reservationroomlinextab.linenumber 
INNER JOIN Rooms WITH (NOLOCK) ON reservationroomline.RoomNumber = Rooms.RoomNumber 
and referencenumber=0
and reservationroomline.roomnumber IN (SELECT DISTINCT RoomNumber FROM Rooms WITH (NOLOCK) 
WHERE Number IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARoomNumberString))
AND HotelNumber = @AHotelNumber)
and convert(datetime, arrivaldate, 112)+convert(datetime, arrivaltime, 108) < @AEnd
and convert(datetime, departuredate, 112)+convert(datetime, departuretime, 108) >= @AStart


