CREATE VIEW dbo.TConfRoomAvailabilityView
AS
/*
  Last Changed
  RN 2013-09-09 CR063921, Added ISNULL stuff for SchemePattern
  RN 2013-07-30 Added Children2 column to view
  BA 2009-08-11 Plenum added to the selection (V4.1+)
  AV 2007 1101 reservationmain,origintype added
  nb20071016 bug Syntax error fixed + optimization
  2006 1128 UserTypeNumber added
  2005-08-24 JP fixed HotelNumber for outoforder, AddressType number done in correct table for speed
*/
SELECT HL.Plenum, R.Status AS MainStatus, HL.ReservationConfLineNumber, HL.BookedConferenceRoomTypeNumber, HL.NumberOfRooms, 
  HL.ArrivalDate, HL.ArrivalTime, HL.DepartureDate, HL.DepartureTime, HL.tablebooking, HL.HallLinkNumber, 
  HL.Price, HL.TotalPrice, HL.PriceOverWritten, HL.ConferenceRoomSetupNumber, HL.ProductNumber, HL.PackageNumber, 
  HL.Adults, HL.Children1, HL.Children2, HL.ReferenceNumber, HT.Text AS TableSetup, HL.ConferenceRoomNumber, ISNULL(HL.SchemePattern,0) AS SchemePattern, HL.SchemeWeekDays,
  HL.TableStatus, HL.GuestNo, HL.AllocationLocked, HL.MeetingSecret,
  (CASE WHEN HL.ConferenceRoomNumber>0 THEN H.HotelNumber ELSE RM.HotelNumber /*maybe this should be from the type?*/ END) AS HotelNumber,
   RM.OriginType,
  1 AS AddressType, -- JP NOTE you were inner joining with ReservationAddress on AddressType=1, what do you think it is?
  HX.CollectsConferenceRoomNumber, P.ProductName, H.Number AS ScreenNumber, 0 AS UserTypeNumber,
  A.Lastname, 
  RA2.LastName AS CompanyName, RA3.LastName AS AgencyName, RA4.LastName AS AgentName,
 (SELECT Number FROM dbo.ConferenceRooms WITH (NOLOCK) where ConferenceRoomNumber= HX.CollectsConferenceRoomNumber) as ScreenHall
 ,ConferenceRoomStatusType.StatusLetter
 ,ConferenceRoomTypes.ResourceType
FROM RESERVATIONS R WITH (NOLOCK)
  INNER JOIN dbo.RESERVATIONCONFERENCELINE HL WITH (NOLOCK) ON R.ReferenceNumber = HL.ReferenceNumber AND R.GUESTNUMBER = 0 
    AND HL.BookedConferenceRoomTypeNumber IS NOT NULL AND ISNULL(SchemePattern,0) = 0 -- IS NULL
  LEFT JOIN dbo.RESERVATIONADDRESSXTAB AX WITH (NOLOCK) ON R.ReferenceNumber = AX.REFERENCENUMBER AND R.GuestNumber = AX.GUESTNUMBER AND AX.AddressTypeNumber=1
  LEFT JOIN dbo.RESERVATIONADDRESS A WITH (NOLOCK) ON  AX.ADDRESSNUMBER=A.AddressNumber
  LEFT JOIN dbo.CONFERENCEROOMS H WITH (NOLOCK) ON HL.ConferenceRoomNumber = H.ConferenceRoomNumber
  LEFT JOIN dbo.CONFERENCEROOMSXTAB HX WITH (NOLOCK) ON HL.ConferenceRoomNumber = HX.Conferenceroomnumber 
  LEFT JOIN dbo.CONFERENCEROOMTABLES HT WITH (NOLOCK) ON HL.ConferenceRoomSetupNumber = HT.ConferenceRoomTableNumber 
  INNER JOIN dbo.ReservationMain RM WITH (NOLOCK) ON RM.ReferenceNumber=R.Referencenumber 
  LEFT JOIN dbo.products P WITH (NOLOCK) ON P.productnumber = HL.Productnumber
  LEFT JOIN dbo.ReservationAddressXTab RAX2 WITH (NOLOCK) ON R.ReferenceNumber=RAX2.ReferenceNumber AND R.GuestNumber=RAX2.GuestNumber AND RAX2.AddressTypeNumber=2
  LEFT JOIN dbo.ReservationAddress RA2 WITH (NOLOCK) ON RAX2.AddressNumber = RA2.AddressNumber
  LEFT JOIN dbo.ReservationAddressXTab RAX3 WITH (NOLOCK) ON R.ReferenceNumber=RAX3.ReferenceNumber AND R.GuestNumber=RAX3.GuestNumber AND RAX3.AddressTypeNumber=3
  LEFT JOIN dbo.ReservationAddress RA3 WITH (NOLOCK) ON RAX3.AddressNumber = RA3.AddressNumber
  LEFT JOIN dbo.ReservationAddressXTab RAX4 WITH (NOLOCK) ON R.ReferenceNumber=RAX4.ReferenceNumber AND R.GuestNumber=RAX4.GuestNumber AND RAX4.AddressTypeNumber=4
  LEFT JOIN dbo.ReservationAddress RA4 WITH (NOLOCK) ON RAX4.AddressNumber = RA4.AddressNumber
  LEFT JOIN dbo.ResvConfLineConfRmStatusXtab WITH (NOLOCK) ON dbo.ResvConfLineConfRmStatusXtab.FK_ReservationConferenceLineNumber = HL.ReservationConfLineNumber
  LEFT JOIN dbo.ConferenceRoomStatusType WITH (NOLOCK) ON dbo.ConferenceRoomStatusType.ConferenceRoomStatusTypeID = dbo.ResvConfLineConfRmStatusXtab.FK_ConferenceRoomStatusTypeID
  LEFT JOIN dbo.ConferenceRoomTypes WITH (NOLOCK) ON dbo.ConferenceRoomTypes.ConferenceRoomTypeNumber = HL.BookedConferenceRoomTypeNumber

