CREATE PROCEDURE dbo.InhouseList @AHotelNumber INT, @AArrivalDate CHAR(8), @ADepartureDate CHAR(8), @AExcludeRoomTypeCSV VARCHAR(500) = '' AS
/*
  Craeted by Kent Johansen, December 2014.
  Purpose: Returns guest info for inhouse guests for specified hotel and period.
  Changes:
    KJ 20151110: Ext: Added sort by ArrivalDate
    KJ 20150527: Res with departure should also be included.
    KJ 20150421: Bug: Fix for "Reservation for excluded type is added when booked type and allocated type is not the same". Case #CR085067
    KJ 20150107: Ext: Added @AExcludeRoomTypeCSV to exclude NOT room types
    KJ 20150105: Wrong sort if First name is blank.

*/
SET NOCOUNT ON
SELECT  RESERVATIONS.ReferenceNumber, RESERVATIONS.GuestNumber, RESERVATIONS.Status,
CASE WHEN ISNULL(raGuest.FirstName, '') <> '' THEN raGuest.FirstName + ' ' + raGuest.LastName ELSE raGuest.LastName END AS FullName,
raGuest.LastName, raGuest.FirstName, 
raGuest.CountryCode AS GuestCountry,
Rooms1.Number AS RoomNumber, Rooms1.Name AS RoomName,
RESERVATIONS.ArrivalDate, 
RESERVATIONS.ArrivalTime, 
RESERVATIONS.DepartureDate, RESERVATIONS.DepartureTime
FROM ReservationRoomLine WITH (NOLOCK)
INNER JOIN ReservationRoomLineXTab WITH (NOLOCK) ON ReservationRoomLine.LineNumber = ReservationRoomLineXTab.LineNumber
INNER JOIN Reservations WITH (NOLOCK) ON ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber
  AND ReservationRoomLineXTab.GuestNumber = RESERVATIONS.GuestNumber
INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = Reservations.ReferenceNumber
INNER JOIN ReservationAddressXTab WITH (NOLOCK) ON Reservations.ReferenceNumber = ReservationAddressXTab.ReferenceNumber
  AND Reservations.GuestNumber = ReservationAddressXTab.GuestNumber
INNER JOIN ReservationAddress raGuest WITH (NOLOCK) ON raGuest.AddressNumber = ReservationAddressXTab.AddressNumber AND AddressType = 1
LEFT OUTER JOIN Rooms Rooms1 WITH (NOLOCK) ON Rooms1.RoomNumber = ReservationRoomLine.RoomNumber
LEFT OUTER JOIN RoomTypes RoomTypes1 WITH (NOLOCK) ON RoomTypes1.RoomTypeNumber = Rooms1.RoomTypeNumber
LEFT OUTER JOIN RoomTypes RoomTypesBooked WITH (NOLOCK) ON RoomTypesBooked.RoomTypeNumber = ReservationRoomLine.BookedTypeNumber
LEFT OUTER JOIN Rooms WITH (NOLOCK) ON Rooms.RoomNumber = RESERVATIONROOMLINE.RoomNumber
WHERE ((
(((RESERVATIONROOMLINE.DepartureDate >= @AArrivalDate) AND (RESERVATIONROOMLINE.ArrivalDate <= @ADepartureDate) AND (Reservations.GuestNumber = 0)) 
OR  ((RESERVATIONS.DepartureDate >= @AArrivalDate) AND (RESERVATIONS.ArrivalDate <= @ADepartureDate)) AND (reservations.GuestNumber > 0)))) 
AND Reservations.Status <= 6
AND RoomTypesBooked.HotelNumber = @AHotelNumber
AND Reservations.Referencenumber > 0
AND RoomTypesBooked.RoomTypeNumber NOT IN (SELECT Number FROM iter_intlist_to_table_No_Dummy(@AExcludeRoomTypeCSV))
AND RoomTypes1.RoomTypeNumber NOT IN (SELECT Number FROM iter_intlist_to_table_No_Dummy(@AExcludeRoomTypeCSV))
ORDER BY Reservations.ArrivalDate, CASE WHEN ISNULL(raGuest.FirstName, '') <> '' THEN raGuest.FirstName + ' ' + raGuest.LastName ELSE raGuest.LastName END