CREATE VIEW DBO.TGenerateAvailabilityView AS
/*
  Created by Jacob Pedersen
  Last Changed:
  JP20070303 OPTIMIZE: removed ReservationMain table as it was not used anymore
  JP20070215 EXT: only select data after 2005
  JP20060627 OPTIMIZE: added missing dbo to a table
  JP20051012 BUG: missing NOLOCK on rooms and roomtypes
  JP20050913 BUG: fixed HotelNumber -> less rows read for OutOfOrder from GenerateAvailability SP
  JP20050905 OPTIMIZE: added status check, departuredate limit, INNER JOIN to remove all res which does not have rooms. Went from > 47k til 8k rows
*/
SELECT     dbo.RESERVATIONS.ReferenceNumber, dbo.RESERVATIONS.GuestNumber, dbo.RESERVATIONMAIN.AllotmentNumber, dbo.RESERVATIONS.ArrivalDate, 
                      dbo.RESERVATIONS.ArrivalTime, dbo.RESERVATIONS.DepartureDate, dbo.RESERVATIONS.DepartureTime, dbo.RESERVATIONS.Adults, 
                      dbo.RESERVATIONS.Children1, dbo.RESERVATIONS.Children2, dbo.RESERVATIONROOMLINE.NumberOfRooms AS SpecNumberOfRooms, 
                      dbo.RESERVATIONROOMLINE.ArrivalDate AS SpecArrivalDate, dbo.RESERVATIONROOMLINE.ArrivalTime AS SpecArrivalTime, 
                      dbo.RESERVATIONROOMLINE.DepartureDate AS SpecDepartureDate, dbo.RESERVATIONROOMLINE.DepartureTime AS SpecDepartureTime, 
                      dbo.RESERVATIONROOMLINE.Adults AS SpecAdults, dbo.RESERVATIONROOMLINE.Children1 AS SpecChildren1, 
                      dbo.RESERVATIONROOMLINE.Children2 AS SpecChildren2, dbo.RESERVATIONROOMLINE.LINENUMBER, 
                      dbo.RESERVATIONROOMLINE.BookedTypeNumber AS SpecBookedTypeNumber, dbo.RESERVATIONROOMLINE.RoomNumber AS SpecRoomNumberID,
                      dbo.RESERVATIONS.POINTOFSALE, dbo.RESERVATIONS.MARKETSEGMENT,
                      dbo.Rooms.CustomerNumber as RoomCustomerNumber, dbo.RESERVATIONADDRESS.LINK AS AddressLink
,CASE WHEN dbo.ReservationRoomLine.RoomNumber>0 THEN dbo.Rooms.HotelNumber ELSE dbo.RoomTypes.HotelNumber END AS HotelNumber
,CASE WHEN dbo.ReservationRoomLine.RoomNumber>0 THEN dbo.Rooms.RoomTypeNumber ELSE dbo.RoomTypes.RoomTypeNumber END AS SpecAllocatedTypeNumber
,CASE RESERVATIONS.GuestNumber 
    WHEN 0 THEN
      CASE ISNULL(RESERVATIONROOMLINE.LineNumber, 0)
      WHEN 0 THEN
        ISNULL(RESERVATIONS.Status,0)
      ELSE 
        CASE ISNULL(RESERVATIONROOMLINE.StatusRL,-1)
        WHEN -1 THEN
          ISNULL(RESERVATIONS.Status,0)
        ELSE 
          CASE
            WHEN ISNULL(RESERVATIONS.Status,0) < 5 THEN
              ISNULL(RESERVATIONROOMLINE.StatusRL,0)
            ELSE 
              ISNULL(RESERVATIONS.Status,0)
          END 
        END 
    END ELSE
      ISNULL(RESERVATIONS.Status,0)
    END AS Status
,dbo.ROOMTYPES.ParentRoomTypeNumber 
,dbo.ReservationRoomLine.Price
,(SELECT COUNT(*) FROM dbo.ReservationRoomLineXTab WITH (NOLOCK) WHERE LineNumber = ReservationRoomLine.LineNumber) AS LinkedRes
,RESERVATIONS.FK_ReservationGroup AS ReservationGroup
,RESERVATIONROOMLINE.FK_ReservationGroup AS SpecReservationGroup
FROM dbo.RESERVATIONROOMLINE WITH (NOLOCK)
INNER JOIN dbo.RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON dbo.RESERVATIONROOMLINE.LINENUMBER = dbo.RESERVATIONROOMLINEXTAB.LineNumber AND dbo.ReservationRoomLine.DepartureDate >= '20051231' -- limit rows which we know we will never need. this means we cannot regenerate availability before 2006
INNER JOIN dbo.RESERVATIONS WITH (NOLOCK) ON (dbo.RESERVATIONS.Status < 7 OR dbo.RESERVATIONS.Status IS NULL)  -- limit rows which we know we will never need
INNER JOIN dbo.RESERVATIONMAIN WITH (NOLOCK) ON dbo.RESERVATIONS.ReferenceNumber = dbo.RESERVATIONMAIN.REFERENCENUMBER 
AND dbo.RESERVATIONROOMLINEXTAB.ReferenceNumber = dbo.RESERVATIONS.ReferenceNumber AND dbo.RESERVATIONROOMLINEXTAB.GuestNumber = dbo.RESERVATIONS.GuestNumber
LEFT OUTER JOIN dbo.Rooms (NOLOCK) ON dbo.Rooms.RoomNumber=dbo.ReservationRoomLine.RoomNumber
LEFT OUTER JOIN dbo.RoomTypes (NOLOCK) ON dbo.RoomTypes.RoomTypeNumber=dbo.ReservationRoomLine.BookedTypeNumber
LEFT OUTER JOIN dbo.RESERVATIONADDRESSXTAB (NOLOCK) ON dbo.RESERVATIONS.ReferenceNumber = dbo.RESERVATIONADDRESSXTAB.REFERENCENUMBER AND dbo.RESERVATIONS.GuestNumber = dbo.RESERVATIONADDRESSXTAB.GUESTNUMBER 
AND RESERVATIONADDRESSXTAB.AddressTypeNumber IN (SELECT TOP 1 AddressTypeNumber 
                                                  FROM ReservationAddressXTab WITH (NOLOCK)
                                                  WHERE ReferenceNumber = Reservations.ReferenceNumber AND
                                                        GuestNumber = Reservations.GuestNumber AND
                                                        (AddressTypeNumber = 1 or (Reservations.referencenumber = 0 or Reservations.referencenumber = -2 or Reservations.referencenumber = -3)))
LEFT OUTER JOIN dbo.RESERVATIONADDRESS (NOLOCK) ON dbo.RESERVATIONADDRESSXTAB.ADDRESSNUMBER = dbo.RESERVATIONADDRESS.AddressNumber 
