CREATE PROCEDURE dbo.ItineraryReport (@AFromDate DATETIME, @AToDate DATETIME, @AHotelNumberCSV VARCHAR(1000), @AReservationTypeCSV VARCHAR(1000), 
  @AReservationStatusCSV VARCHAR(1000), @AIncludeAllocated BIT) AS
/*
  Created by Kent Johansen december 2013
  Get list of itinerary reservations (end-to-end Reservations for same person)
  
  Changes:

*/

WITH Itinerary (ReferenceNumber, ReferenceNumber2, LastName, FirstName, ArrivalDate, DepartureDate, Idx) AS
(
  SELECT Ref1, -1, LastName, FirstName, ArrivalDate, DepartureDate, 0 
  FROM dbo.ItineraryBase(@AFromDate, @AToDate, @AHotelNumberCSV, @AReservationTypeCSV, @AReservationStatusCSV, @AIncludeAllocated)
  
  UNION ALL 

  SELECT r2.ReferenceNumber, Itinerary.ReferenceNumber, ra2.LastName, ra2.FirstName, r2.ArrivalDate, r2.DepartureDate, idx+1
  FROM Reservations r2 WITH (NOLOCK)  
  INNER JOIN ReservationAddressXTab rax2 WITH (NOLOCK) ON rax2.ReferenceNumber = r2.ReferenceNumber
    AND rax2.GuestNumber = r2.GuestNumber
  INNER JOIN ReservationAddress ra2 WITH (NOLOCK) ON ra2.AddressNumber = rax2.AddressNumber
    AND ra2.AddressType = 1
  INNER JOIN Itinerary ON Itinerary.DepartureDate = r2.ArrivalDate
    AND Itinerary.LastName = ra2.LastName
    AND Itinerary.FirstName = ra2.FirstName
  WHERE Itinerary.ReferenceNumber <> r2.ReferenceNumber  
)

SELECT * 
FROM Itinerary