CREATE PROCEDURE DBO.Hoist @AHotelNumber INT, @ArrivalDate CHAR(8), @DepartureDate CHAR(8), @ARoomNumber INT AS
SET NOCOUNT ON
/*
  Created by Kent Johansen, April 2013
  Changes:
    KJ 20160831: Reservation notes per hotel. Req #3032
    KJ 20131202: Ext: Added notes
    KJ 20130909: Bug: Fix for "Reservation shown multiple times"
    Bug: Fix for "Showing wrong guests".
  
*/

SELECT  R.ReferenceNumber, R.GuestNumber, R.Status, ReservationMain.HotelNumber,
Rooms1.Number AS RoomNumber,
ReservationAddress.FirstName, ReservationAddress.LastName, ReservationAddress.Phone, ReservationAddress.Cell,
ReservationAddress.Email, Countries.CountryName,
CASE R.GuestNumber WHEN 0 THEN RRL.ArrivalDate ELSE R.ArrivalDate END AS ArrivalDate, 
CASE R.GuestNumber WHEN 0 THEN RRL.ArrivalTime ELSE R.ArrivalTime END AS ArrivalTime, 
CASE R.GuestNumber WHEN 0 THEN RRL.DepartureDate ELSE R.DepartureDate END AS DepartureDate,
CASE R.GuestNumber WHEN 0 THEN RRL.DepartureTime ELSE R.DepartureTime END AS DepartureTime,
CASE R.GuestNumber WHEN 0 THEN RRL.Adults ELSE R.Adults END AS Adults, 
CASE R.GuestNumber WHEN 0 THEN RRL.Children1 ELSE R.Children1 END AS Children1, 
CASE R.GuestNumber WHEN 0 THEN RRL.Children2 ELSE R.Children2 END AS Children2,
r1.Text AS Note1, r2.Text AS Note2, r3.Text AS Note3
FROM Reservations R WITH (NOLOCK)
INNER JOIN ReservationRoomLineXtab WITH (NOLOCK) ON ReservationRoomLineXtab.ReferenceNumber = R.ReferenceNumber
  AND ReservationRoomLineXtab.GuestNumber = R.GuestNumber
INNER JOIN ReservationRoomLine RRL WITH (NOLOCK) ON RRL.LineNumber = ReservationRoomLineXtab.LineNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = R.ReferenceNumber
LEFT OUTER JOIN Rooms Rooms1 WITH (NOLOCK) ON Rooms1.RoomNumber = RRL.RoomNumber
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = R.ReferenceNumber
  AND ReservationAddressXTab.GuestNumber = R.GuestNumber
  AND ReservationAddressXTab.AddressTypeNumber = 1
INNER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
LEFT OUTER JOIN Countries WITH (NOLOCK) ON Countries.CountryNumber = ReservationAddress.CountryNumber
LEFT OUTER JOIN ReservationNotes r1 WITH (NOLOCK) ON r1.ReferenceNumber = R.ReferenceNumber
  AND r1.GuestNumber = R.GuestNumber
  AND r1.NotePage = 1
LEFT OUTER JOIN ReservationNotes r2 WITH (NOLOCK) ON r2.ReferenceNumber = R.ReferenceNumber 
  AND r2.GuestNumber = R.GuestNumber
  AND r2.NotePage = 2
LEFT OUTER JOIN ReservationNotes r3 WITH (NOLOCK) ON r3.ReferenceNumber = R.ReferenceNumber 
  AND r3.GuestNumber = R.GuestNumber
  AND r3.NotePage = 3
WHERE
((RRL.ArrivalDate >= @ArrivalDate AND RRL.ArrivalDate <= @DepartureDate AND R.GuestNumber = 0)
OR (R.ArrivalDate >= @ArrivalDate AND R.ArrivalDate <= @DepartureDate AND R.GuestNumber > 0))
AND ReservationMain.HotelNumber = @AHotelNumber
AND R.Referencenumber > 0
AND (ISNULL(@ARoomNumber, 0) <= 0 OR Rooms1.Number = @ARoomNumber)