USE MyDatabase

WITH CTE
AS
(

    SELECT 
       a.ReservationStayID,
       c.Createdon,
       a.FirstName + ' ' + a.LastName AS 'Name',
       b.PropertyCode AS'Property',
       b.ReservationStatus AS 'Status',
       d.rsl_rateplan AS 'Rate Plan Code',
       d.rsl_mealplan,
       d.rsl_roomtype,
       b.MarketSegmentCode AS 'Market Segment',
       e.TravelAgencyTypeCode AS 'Source of Business',
       a.ArrivalDate AS 'Date of Arrival',
       a.DepartureDate AS 'Date of Departure',
       (SELECT DATEDIFF (day,ArrivalDate,DepartureDate) 
        FROM ReservationStay c
        WHERE c.ReservationStayID = a.ReservationStayID) AS 'Nights',
       c.AdultCount AS 'Adult',
       c.ChildCount AS 'Child',
       b.GuestCount AS 'Guest',
       d.rsl_reasonstay,
       c.TaProfileID,    
       c.PMSConfirmationNumber,
       c.CurrencyCode As 'Currency',
       d.rsl_nationality AS 'Nationality',
       d.rsl_country AS 'Country of Residence',
       d.rsl_totalroomrate,
       e.Name AS 'Tour Operator',
       e.CountryCode AS 'Market Code',    
       g.CountryGroup AS 'Market',


     (SELECT avg(RateAmount)/1.15
      FROM ReservationStayDate f
      where f.ReservationStayID = a.ReservationStayID) AS 'Rate Amount Excl.VAT',

      c.TAProfileID2,
      e2.Name AS 'Booking Origin (1)',
      g2.CountryGroup AS 'Booking Origin (2)'



    FROM GuestNameInfo a
     JOIN GuestStaySummary b ON a.ReservationStayID = b.ReservationStayID
     LEFT JOIN ReservationStay c ON c.ReservationStayID = b.ReservationStayID
     LEFT JOIN P5RESERVATIONLIST d ON d.rsl_code = b.ReservationStayID
     LEFT JOIN TravelAgency e ON e.TravelAgencyID = c.TAProfileID
     LEFT JOIN Market g ON e.CountryCode = g.CountryCode
     LEFT JOIN TravelAgency e2 ON e2.TravelAgencyID = c.TAProfileID2
     LEFT JOIN Market g2 ON e2.CountryCode = g2.CountryCode

     LEFT JOIN
     (
       SELECT 
         ReservationStayID,
         datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar) as [MTH],
         count(*) AS [Nights Spent],
         avg(RateAmount) as [Rate],
         min(CreatedOn) as CreatedOn,
         min(StayDate) as [DateOfArrival],
         max(StayDate) as [DateOfDeparture]
         FROM ReservationStayDate
        GROUP BY ReservationStayID, datename(m,StayDate) + ' ' + cast(datepart(yyyy,StayDate) as varchar)
       ) x ON x.ReservationStayID = b.ReservationStayID

    WHERE a.PrimaryGuest='+' and d.rsl_primaryguest='+'
)

SELECT 
  m.*,
  p.PkgRev,
  p.GADR
FROM CTE AS m

INNER JOIN dbo.Budget1415 AS p  ON m.PropertyCode = p.PropertyCode AND m.[MTH] = p.Mth AND m.countrygroup = p.Mkt

SELECT 
  m.*,
  p.PkgRev,
  p.GADR
FROM CTE AS m

INNER JOIN dbo.Budget1415 AS p  ON m.PropertyCode = p.PropertyCode AND m.[MTH] = p.Mth AND m.[Market] = p.Mkt