CREATE FUNCTION dbo.ItineraryBase (@AFromDate DATETIME, @AToDate DATETIME, @AHotelNumberCSV VARCHAR(1000), @AReservationTypeCSV VARCHAR(1000), 
  @AReservationStatusCSV VARCHAR(1000), @AIncludeAllocated BIT) RETURNS @Result TABLE 
(
  Ref1 INT,
  Ref2 INT, 
  LastName VARCHAR(50),
  FirstName VARCHAR(50),
  ArrivalDate CHAR(8),
  DepartureDate CHAR(8)
) AS
BEGIN
/*
  Created by Kent Johansen december 2013
  Get list of itinerary reservations (end-to-end Reservations for same person)
  This is the Anchor part of the cte (Common Table Expression)
  
  Changes:

*/
  DECLARE @Ref1 INT
  DECLARE @Ref2 INT
  DECLARE @LastName VARCHAR(50)
  DECLARE @FirstName VARCHAR(50)
  DECLARE @ArrivalDate CHAR(8)
  DECLARE @DepartureDate CHAR(8)

  DECLARE db_cur CURSOR FOR 
    SELECT r1.ReferenceNumber, r2.ReferenceNumber, ra1.LastName, ra1.FirstName, r1.ArrivalDate, r1.DepartureDate
    FROM Reservations r1 WITH (NOLOCK)
    INNER JOIN ReservationRoomLineXtab rrlx WITH (NOLOCK) ON rrlx.ReferenceNumber = r1.ReferenceNumber
      AND rrlx.GuestNumber = r1.GuestNumber
    INNER JOIN ReservationRoomLine rrl WITH (NOLOCK) ON rrl.LineNumber = rrlx.LineNumber
      AND (@AIncludeAllocated = 1 OR (ISNULL(rrl.RoomNumber, 0) = 0))
    INNER JOIN ReservationMain WITH (NOLOCK) ON ReservationMain.ReferenceNumber = r1.ReferenceNumber
      AND ReservationMain.ReservationType IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AReservationTypeCSV))
      AND (@AHotelNumberCSV = '' OR ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV)))
    INNER JOIN ReservationAddressXTab rax1 WITH (NOLOCK) ON rax1.ReferenceNumber = r1.ReferenceNumber
      AND rax1.GuestNumber = r1.GuestNumber
    INNER JOIN ReservationAddress ra1 WITH (NOLOCK) ON ra1.AddressNumber = rax1.AddressNumber
      AND ra1.AddressType = 1
    INNER JOIN Reservations r2 WITH (NOLOCK) ON r1.DepartureDate = r2.ArrivalDate
      AND r1.ReferenceNumber <> r2.ReferenceNumber
      AND r1.ReferenceNumber > 0
      AND r2.ReferenceNumber > 0
      AND r1.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AReservationStatusCSV))
      AND r2.Status IN (SELECT DISTINCT Number FROM iter_intlist_to_table_No_Dummy(@AReservationStatusCSV))
    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
      AND ra1.LastName = ra2.LastName  
      AND ra1.FirstName = ra2.FirstName  
    WHERE r1.ArrivalDate >= CONVERT(CHAR(8), @AFromDate, 112)
    AND r1.ArrivalDate <= CONVERT(CHAR(8), @AToDate, 112)
    ORDER BY r1.ArrivalDate

  OPEN db_cur
  FETCH NEXT FROM db_cur INTO @Ref1, @Ref2, @LastName, @FirstName, @ArrivalDate, @DepartureDate

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF NOT EXISTS (SELECT * FROM @Result WHERE Ref2 = @Ref1)
      INSERT INTO @Result VALUES (@Ref1, @Ref2, @LastName, @FirstName, @ArrivalDate, @DepartureDate)
    FETCH NEXT FROM db_cur INTO @Ref1, @Ref2, @LastName, @FirstName, @ArrivalDate, @DepartureDate
  END
 
  CLOSE db_cur
  DEALLOCATE db_cur

  RETURN
END