CREATE PROCEDURE dbo.InhouseListHall @AHotelNumber INT, @AArrivalDate CHAR(8), @ADepartureDate CHAR(8), @AGroupNumberCSV VARCHAR(100) = '' AS
/*
  Craeted by Kent Johansen, January 2015.
  Purpose: Returns hall info for reservations arrival for specified hotel and period.
  Changes:
    SG 20200713: Added agency
    KJ 20160414: Added Group
    KJ 20160303: Include res without company.

*/
SET NOCOUNT ON

DECLARE @AArrivalDateAddOne CHAR(8)
SET @AArrivalDateAddOne = CONVERT(CHAR(8), DATEADD(DAY, 1, CONVERT(DATETIME, @AArrivalDate, 112)), 112)

DECLARE @Arr24 CHAR(5)
SELECT @Arr24 = KeyValue FROM HotelSetup WITH (NOLOCK) WHERE SearchKey = 'Arr24HoursStarts'
IF ISNULL(@Arr24, '') = ''
  SET @Arr24 = '07:00'

SELECT ReservationConferenceLine.ReferenceNumber, ISNULL(ReservationConferenceLine.GuestNo, 0) AS GuestNumber, Reservations.Status,
CASE WHEN ISNULL(raFirm.LastName, '') <> '' THEN
  raFirm.LastName
ELSE
  CASE WHEN ISNULL(raGuest.FirstName, '') <> '' THEN raGuest.FirstName + ' ' + raGuest.LastName ELSE raGuest.LastName END
END AS FullName,
raFirm.LastName AS CompanyLastName,
raAgency.LastName AS AgencyName,
raGuest.LastName AS GuestLastName, raGuest.FirstName AS GuestFirstName,
ConferenceRooms.Number, ConferenceRooms.Name,
ReservationConferenceLine.ArrivalDate, ReservationConferenceLine.ArrivalTime, ReservationConferenceLine.DepartureDate,
ReservationConferenceLine.DepartureTime, ConferenceRoomMonetXTab.MonetGroupNumber AS GroupNumber, ConferenceRoomMonetXTab.Filename
FROM Reservations WITH (NOLOCK)
INNER JOIN ReservationConferenceLine WITH (NOLOCK) ON ReservationConferenceLine.ReferenceNumber = Reservations.ReferenceNumber
  AND ISNULL(ReservationConferenceLine.GuestNo, 0) = Reservations.GuestNumber
INNER JOIN ConferenceRooms WITH (NOLOCK) ON ConferenceRooms.ConferenceRoomNumber = ReservationConferenceLine.ConferenceRoomNumber
  AND ConferenceRooms.HotelNumber = @AHotelNumber
INNER JOIN ConferenceRoomMonetXTab WITH (NOLOCK) ON CONFERENCEROOMS.ConferenceRoomNumber = ConferenceRoomMonetXTab.ConferenceRoomNumber
INNER JOIN ReservationAddressXTab rax1 WITH (NOLOCK) ON rax1.ReferenceNumber = Reservations.ReferenceNumber
  AND rax1.GuestNumber = Reservations.GuestNumber
  AND rax1.AddressTypeNumber = 1
INNER JOIN ReservationAddress raGuest WITH (NOLOCK) ON raGuest.AddressNumber = rax1.AddressNumber
LEFT OUTER JOIN ReservationAddressXTab rax2 WITH (NOLOCK) ON rax2.ReferenceNumber = Reservations.ReferenceNumber
  AND rax2.GuestNumber = Reservations.GuestNumber
  AND rax2.AddressTypeNumber = 2  
LEFT OUTER JOIN ReservationAddress raFirm WITH (NOLOCK) ON raFirm.AddressNumber = rax2.AddressNumber
--Agency
LEFT OUTER JOIN ReservationAddressXTab rax3 WITH (NOLOCK) ON rax3.ReferenceNumber = Reservations.ReferenceNumber
  AND rax3.GuestNumber = Reservations.GuestNumber
  AND rax3.AddressTypeNumber = 3  
LEFT OUTER JOIN ReservationAddress raAgency WITH (NOLOCK) ON raAgency.AddressNumber = rax3.AddressNumber

WHERE (((ReservationConferenceLine.ArrivalDate = @AArrivalDate AND ReservationConferenceLine.ArrivalTime >= @Arr24)
  OR (ReservationConferenceLine.ArrivalDate=@AArrivalDateAddOne AND ReservationConferenceLine.ArrivalTime<@Arr24))
OR (ReservationConferenceLine.ArrivalDate>@AArrivalDate AND ReservationConferenceLine.ArrivalDate<=@ADepartureDate))
AND ((ISNULL(ReservationConferenceLine.SchemePattern, 0) = 0) or not (ReservationConferenceLine.LinkLineNumber is null))
AND Reservations.ReferenceNumber > 0
AND Reservations.Status <= 6
AND ISNULL(ReservationConferenceLine.TableBooking, 0) = 0
AND ((@AGroupNumberCSV = '') OR (@AGroupNumberCSV <> '' AND ISNULL(ConferenceRooms.GroupNumber, 0) IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AGroupNumberCSV))))