CREATE PROCEDURE [dbo].[GetReservationDates] @FromDate datetime, @ToDate datetime, @RoomNumberID INT
AS
SET NOCOUNT ON

DECLARE @FormattedFromDate VARCHAR(8),
@FormattedToDate VARCHAR(8)

SET @FormattedFromDate = CONVERT (CHAR(8), @FromDate, 112)
SET @FormattedToDate = CONVERT (CHAR(8), @ToDate, 112);

WITH T1
AS
(
SELECT
  r.ReferenceNumber,
  CASE WHEN r.Status < 5 THEN rl.StatusRL ELSE r.Status END Status,
  CASE WHEN r.GuestNumber > 0 THEN r.ArrivalDate else rl.ArrivalDate END ArrivalDate,
  CASE WHEN r.GuestNumber > 0 THEN r.ArrivalTime else rl.ArrivalTime END ArrivalTime,
  CASE WHEN r.GuestNumber > 0 THEN r.DepartureDate else rl.DepartureDate END DepartureDate,
  CASE WHEN r.GuestNumber > 0 THEN r.DepartureTime else rl.DepartureTime END DepartureTime,
  rl.RoomNumber RoomNumberID
FROM ReservationRoomline rl (NOLOCK)
INNER JOIN RESERVATIONROOMLINEXTAB rlx (NOLOCK) ON rl.LINENUMBER = rlx.LINENUMBER
INNER JOIN RESERVATIONS r (NOLOCK) ON rlx.ReferenceNumber = r.ReferenceNumber AND rlx.GuestNumber = r.GuestNumber
WHERE (@RoomNumberID = -1 OR rl.RoomNumber = @RoomNumberID)
),
T2
AS
(
SELECT
  T1.ArrivalDate, T1.ArrivalTime, 
  T1.DepartureDate, T1.DepartureTime,
  T1.RoomNumberID, T1.Status,
  CASE WHEN T1.ReferenceNumber > 0 THEN 0 ELSE 1 END AS OutOfOrder
  FROM T1
  WHERE T1.ArrivalDate <= @FormattedToDate AND 
	    T1.DepartureDate >= @FormattedFromDate 
),
T3
AS
(
  SELECT T2.ArrivalDate, T2.ArrivalTime, 
  T2.DepartureDate, T2.DepartureTime,
  T2.RoomNumberID, T2.OutOfOrder,
  CASE WHEN T2.OutOfOrder = 0 AND T2.Status = 5 THEN 1 ELSE 0 END AS InHouse
  FROM T2
)

SELECT * from T3
ORDER BY T3.ArrivalDate