CREATE VIEW DBO.TRoomAvailabilityFX AS
/*
  Changes:
  20050114 JP: made SQL shorter and more clear. plus added a few static WHERE's to join part
  20050113 JP: added HotelNumber for use in TRoomAvailabilityFX class. Needed on checkin via arrivals plan
  20041210 JP: rearranged and cleanedup things plus optimized alittle. Also removed Rooms, and RoomType fields
  20041126 JP: optimized lastname and firstname so we don't have to use TReservationAddressXTabView
  20041103 JP: renamed ReservationAddressXTabView
  20041004 JP: fixed missing reservations which has no guestaddress
*/

SELECT RLX.ReferenceNumber, RLX.GuestNumber, R.Status, R.ArrivalDate AS ArrDate, R.ArrivalTime AS ArrTime, R.DepartureDate AS DepDate, 
R.DepartureTime AS DepTime, R.Adults, R.Children1, R.Children2, R.SideHouse, R.Day2, R.ProductNumber,

RL.LineNumber, RL.RoomNumber, RL.ArrivalDate, RL.ArrivalTime, RL.DepartureDate, RL.DepartureTime, 
RL.Adults AS SpecAdults, RL.Children1 AS SpecChildren1,  RL.Children2 AS SpecChildren2, RL.Day2 AS SpecDay2,  RL.ProductNumber AS SpecProductNumber, 

Rooms.HotelNumber AS HotelNumber, Rooms.RoomTypeNumber AS AllocatedTypeNumber, Rooms.SideHouseNumber,
Products_1.ProductName, 
Products.ProductName AS SpecProductName,

-- Guest
RA1.FirstName,
RA1.LastName,

RowsOnRes=CASE RLX.ReferenceNumber -- Unknown if this code is faster than always doing an inner select
WHEN 0 THEN (SELECT COUNT(*) FROM ReservationRoomLineXtab A WITH (NOLOCK) WHERE A.ReferenceNumber=RLX.ReferenceNumber AND A.GuestNumber=RLX.GuestNumber)
ELSE 1
END,

SubResCount=CASE RLX.ReferenceNumber  -- Unknown if this code is faster than always doing an inner select
WHEN 0 THEN (SELECT COUNT(*) FROM ReservationRoomLineXtab A WITH (NOLOCK) WHERE A.ReferenceNumber=RLX.ReferenceNumber AND A.GuestNumber > 0)
ELSE 0
END

FROM RESERVATIONROOMLINEXTAB RLX WITH (NOLOCK)
INNER JOIN RESERVATIONROOMLINE RL WITH (NOLOCK) ON RLX.ReferenceNumber<>-1 AND RL.LINENUMBER = RLX.LineNumber AND RL.RoomNumber > 0
INNER JOIN Rooms WITH (NOLOCK) ON RL.RoomNumber = Rooms.RoomNumber
--INNER JOIN RoomTypes (NOLOCK) ON Rooms.RoomTypeNumber = RoomTypes.RoomTypeNumber

INNER JOIN RESERVATIONS R WITH (NOLOCK) ON Status<7 AND RLX.ReferenceNumber = R.ReferenceNumber AND RLX.GuestNumber = R.GuestNumber

LEFT OUTER JOIN ReservationAddressXTab RAX1 WITH (NOLOCK) ON R.ReferenceNumber=RAX1.ReferenceNumber AND R.GuestNumber=RAX1.GuestNumber AND RAX1.AddressTypeNumber=1 -- Guest
LEFT OUTER JOIN ReservationAddress RA1 WITH (NOLOCK) ON RAX1.AddressNumber = RA1.AddressNumber

LEFT OUTER JOIN Products WITH (NOLOCK) ON Products.ProductNumber = RL.ProductNumber 
LEFT OUTER JOIN Products Products_1 WITH (NOLOCK) ON R.ProductNumber = Products_1.ProductNumber

