
CREATE VIEW DBO.TReservationReportView
AS
SELECT     (SELECT     Cast(Text AS VarChar(5000))
                       FROM          ReserVationNotes a WITH (Nolock)
                       WHERE      a.referencenumber = reservations.referencenumber AND a.guestnumber = reservations.guestnumber AND a.notepage = 1) AS Note, 
                      dbo.RESERVATIONS.ReferenceNumber, dbo.RESERVATIONS.GuestNumber, dbo.RESERVATIONS.Status,
                          (SELECT     MIN(arrivaldate)
                            FROM          reservationroomline WITH (nolock)
                            WHERE      linenumber IN
                                                       (SELECT     linenumber
                                                         FROM          reservationroomlinextab a WITH (nolock)
                                                         WHERE      referencenumber = reservations.referencenumber)) AS TotalArrivalDate,
                      CASE ((SELECT top 1 GuestNumber From RESERVATIONROOMLINEXTAB WITH (NOLOCK) where LineNumber = RESERVATIONROOMLINE.LINENUMBER))
                        WHEN Reservations.GuestNumber then dbo.RESERVATIONROOMLINE.NumberOfRooms
                      ELSE  
                        '0'
                      END
                      AS SpecNumberOfRooms,
                          (SELECT     MAX(departuredate)
                            FROM          reservationroomline WITH (nolock)
                            WHERE      linenumber IN
                                                       (SELECT     linenumber
                                                         FROM          reservationroomlinextab a WITH (nolock)
                                                         WHERE      referencenumber = reservations.referencenumber)) AS TotalDepartureDate,
                          (SELECT   top 1  LastName
                            FROM          ReservationAddress WITH (NOLOCK)
                            WHERE      (AddressNumber IN
                                                       (SELECT top 1    AddressNumber
                                                         FROM          ReservationAddressXTab WITH (NOLOCK)
                                                         WHERE      ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND 
                                                   AddressType = 1) AS LastName,
                          (SELECT   top 1  FirstName
                            FROM          ReservationAddress WITH (NOLOCK)
                            WHERE      (AddressNumber IN
                                                       (SELECT top 1    AddressNumber
                                                         FROM          ReservationAddressXTab WITH (NOLOCK)
                                                         WHERE      ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND 
                                                   AddressType = 1) AS FirstName, dbo.RESERVATIONS.AltResNo, dbo.RESERVATIONS.ArrivalDate, dbo.RESERVATIONS.ArrivalTime, 
                      dbo.RESERVATIONS.DepartureDate, dbo.RESERVATIONS.DepartureTime, dbo.RESERVATIONS.Adults, dbo.RESERVATIONS.Children1, 
                      dbo.RESERVATIONS.Children2,
                          (SELECT     Number
                            FROM          Rooms WITH (NOLOCK)
                            WHERE      Rooms.RoomNumber = ReservationRoomLine.RoomNumber) AS SpecRoomNumber, 
                      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,
                          (SELECT  top 1   LastName
                            FROM          ReservationAddress WITH (NOLOCK)
                            WHERE      (AddressNumber IN
                                                       (SELECT  top 1   AddressNumber
                                                         FROM          ReservationAddressXTab WITH (NOLOCK)
                                                         WHERE      ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND 
                                                   AddressType = 2) AS CompanyName,
                          (SELECT  top 1   LastName
                            FROM          ReservationAddress WITH (NOLOCK)
                            WHERE      (AddressNumber IN
                                                       (SELECT  top 1   AddressNumber
                                                         FROM          ReservationAddressXTab WITH (NOLOCK)
                                                         WHERE      ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND 
                                                   AddressType = 3) AS AgencyName, 
                          (SELECT     Type
                            FROM          RoomTypes WITH (NOLOCK)
                            WHERE      RoomTypeNumber = ReservationRoomline.BookedTypeNumber) AS BookedType,
                          (SELECT     Type
                            FROM          RoomTypes WITH (NOLOCK)
                            WHERE      RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) AS SpecBookedType, dbo.RESERVATIONMAIN.HOTELNUMBER,
                          (SELECT     CountryCode
                            FROM          ReservationAddress WITH (NOLOCK)
                            WHERE      (AddressNumber IN
                                                       (SELECT  top 1   AddressNumber
                                                         FROM          ReservationAddressXTab WITH (NOLOCK)
                                                         WHERE      ReferenceNumber = Reservations.ReferenceNumber AND GuestNumber = Reservations.GuestNumber)) AND 
                                                   AddressType = 1) AS CountryCode,
                          (SELECT     ProductName
                            FROM          Products WITH (NOLOCK)
                            WHERE      Products.ProductNumber = ReservationRoomLine.ProductNumber) AS SpecProduct, dbo.RESERVATIONROOMLINE.Day2 AS SpecDay2, 
                      dbo.RESERVATIONROOMLINE.LINENUMBER, dbo.RESERVATIONMAIN.ReservationType, 
                      dbo.RESERVATIONROOMLINE.ProductNumber AS SpecProductNumber, dbo.RESERVATIONROOMLINE.Price AS SpecPrice, dbo.RESERVATIONS.Price, 
                      dbo.RESERVATIONROOMLINE.TotalPrice AS SpecTotalPrice, dbo.RESERVATIONS.TotalPrice,
                          (SELECT     Text
                            FROM          GuestTypes WITH (NOLOCK)
                            WHERE      TypeNumber = Reservations.GuestType) AS GuestType, dbo.RESERVATIONS.CREDITMAX,
                          (SELECT     Type
                            FROM          RoomTypes WITH (NOLOCK)
                            WHERE      RoomTypeNumber =
                                                       (SELECT     RoomTypeNumber
                                                         FROM          Rooms WITH (NOLOCK)
                                                         WHERE      Rooms.RoomNumber = ReservationRoomLine.RoomNumber)) AS AllocatedType,
                      dbo.RESERVATIONROOMLINE.BookedTypeNumber AS SpecBookedTypeNumber, dbo.RESERVATIONROOMLINE.RoomNumber AS SpecRoomNumberID,
                       dbo.RESERVATIONS.POINTOFSALE, dbo.RESERVATIONS.MARKETSEGMENT,
                          (SELECT     ProductName

                            FROM          Products WITH (NOLOCK)
                            WHERE      Products.ProductNumber = Reservations.ProductNumber) AS Product, dbo.RESERVATIONS.Day2, dbo.RESERVATIONS.ProductNumber,
                          (SELECT     COUNT(*)

                            FROM          ReservationRoomLineXTab WITH (NOLOCK)
                            WHERE      LineNumber = ReservationRoomLine.LineNumber) AS LinkedRes,dbo.ALLOTMENTS.Name AS AllotmentName, 
                      dbo.RESERVATIONS.METHODOFPAYMENT,
                          (SELECT     COUNT(*)
                            FROM          ReservationRoomLineXTab WITH (NOLOCK)
                            WHERE      ReservationRoomLineXTab.ReferenceNumber = Reservations.ReferenceNumber) AS NumberOfRoomlines
FROM         dbo.RESERVATIONMAIN WITH (NOLOCK) INNER JOIN
                      dbo.RESERVATIONS WITH (NOLOCK) ON dbo.RESERVATIONMAIN.REFERENCENUMBER = dbo.RESERVATIONS.ReferenceNumber INNER JOIN
                      dbo.RESERVATIONADDRESSXTAB WITH (NOLOCK) ON 
                      dbo.RESERVATIONS.ReferenceNumber = dbo.RESERVATIONADDRESSXTAB.REFERENCENUMBER AND 
                      dbo.RESERVATIONS.GuestNumber = dbo.RESERVATIONADDRESSXTAB.GUESTNUMBER INNER JOIN
                      dbo.RESERVATIONADDRESS WITH (NOLOCK) ON 
                      dbo.RESERVATIONADDRESSXTAB.ADDRESSNUMBER = dbo.RESERVATIONADDRESS.AddressNumber 
                      LEFT OUTER JOIN dbo.ALLOTMENTS WITH (NOLOCK) ON dbo.RESERVATIONMAIN.AllotmentNumber = dbo.ALLOTMENTS.AllotmentNumber LEFT OUTER JOIN
                      dbo.RESERVATIONROOMLINE WITH (NOLOCK) INNER JOIN
                      dbo.RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON 
                      dbo.RESERVATIONROOMLINE.LINENUMBER = dbo.RESERVATIONROOMLINEXTAB.LINENUMBER ON 
                      dbo.RESERVATIONS.ReferenceNumber = dbo.RESERVATIONROOMLINEXTAB.ReferenceNumber AND 
                      dbo.RESERVATIONS.GuestNumber = dbo.RESERVATIONROOMLINEXTAB.GuestNumber
WHERE     (dbo.RESERVATIONROOMLINE.NumberOfRooms > 0)

