CREATE PROCEDURE dbo.SelectCustomerOrderBookNew @ACustomerNumber INT, @ADateFrom VARCHAR(8), @ADateTo VARCHAR(8), @ASpecBy TINYINT, @AYearSort BIT, @ASegments VARCHAR(MAX), @AHotels VARCHAR(MAX), @AReservationStatuses VARCHAR(128), @ACorrection BIT AS
SET NOCOUNT ON 

/*
  Created by Kent Johansen, September 2023
  Copy of SelectCustomerOrderBook

  ACustomerNumber : Customer to select OrderBook for
  ADateFrom       : Start OrderBook
  ADateTo         : End OrderBook
  ASpecBy         : 0 = Year, 1 = Month, 2 = Week
  AYearSort       : 0 = Ascending, 1 = Descending
  AMonthSort      : 0 = Ascending, 1 = Descending
  AHotels         : OrderBook per hotels
  ASegments       : OrderBook per segments

  Latest changes:
    KJ 20230911: Rewrite to use MIS for orderbook. Copy of SelectCustomerOrderBook
    VD 20170907: It's now possible to specify on weeks. Furthermore some housekeeping
    SG 20161101: CR 113756: Segments and Hotels -> VarChar(MAX)
    VD 20121119: CLN. Housekeeping and optimizing
    VD 20121119: CLN. Housekeeping and optimizing
    OF 20091029: Ext: ContactPersonCustomerNumber in where clause.
    OF 20091005: DownLinks added in query.
    OF 20091002: Changed made for detailed orderbook. Corrections added.
    KJ 20090331: ReservationStatus added
*/

DECLARE 
  @SegmentNumber INT,
  @CorrectionStr VARCHAR(3),

  @TmpSegmentNumber INT,
  @TmpYear INT, 
  @TmpMonth INT,
  @TmpWeek INT,
  @CurSegmentNumber INT,
  @CurYear INT,
  @CurMonth INT,
  @CurWeek INT,
  @FirstPass BIT,
  @CurDateForValue VARCHAR(8),
  @CurHotelCustomerNumber INT,

  @CustomerNumber INT,
  @Total money,
  @Year INT,

  @SelectStmt VARCHAR(1000),
  @FromStmt VARCHAR(1000),
  @WhereStmt VARCHAR(1000),
  @GroupByStmt VARCHAR(1000),
  @OrderByStmt VARCHAR(1000),
  @MemTableName VARCHAR(100)

SET @CorrectionStr = '0,1'
IF @ACorrection = 0
  SET @CorrectionStr = '0'
  
IF @AReservationStatuses = ''  
  SET @AReservationStatuses = '-2'
 
CREATE Table #CustomerLinks(CustomerNumber INT NOT NULL)
INSERT INTO #CustomerLinks(CustomerNumber) EXEC FindOrderBookCustomerLinks @ACustomerNumber
INSERT INTO #CustomerLinks(CustomerNumber) VALUES (@ACustomerNumber)

CREATE TABLE #HotelNumbers(CustomerNumber INT NOT NULL)
INSERT INTO #HotelNumbers
SELECT CustomerNumber 
FROM Customer WITH (NOLOCK)
WHERE CustomerTypeNumber = 7
AND (@AHotels = '' OR Customer.CustomerNumber IN (SELECT Value FROM SplitNumberList(@AHotels)))

IF NOT EXISTS(SELECT * FROM #HotelNumbers WHERE CustomerNumber = -1)
  INSERT INTO #HotelNumbers VALUES (-1) 

IF @ASpecBy < 2  
BEGIN
  SELECT ISNULL(ISNULL(ReservationAddress.Link, ReservationAddress.ContactPersonCustomerNumber), AddressFolio.Link) AS CustomerNumber, MISTable.SegmentNumber, MISTable.DateStamp AS DateForValue, MISTable.HotelCustomerNumber, 
  DATEPART(YEAR, MISTable.DateStamp) AS Year, 
  DATEPART(MONTH, MISTable.DateStamp) AS Month,
  CASE WHEN MISTable.ValueType = 1 THEN SUM(BookedPrice) + SUM(ActualPrice) +  SUM(ActualPackage) ELSE 0 END AS TurnoverRooms,
  CASE WHEN MISTable.ValueType = 1 THEN SUM(BookedNumberOfRooms) + SUM(ActualNumberOfRooms) ELSE 0 END AS NumberOfNightRooms,
  CASE WHEN MISTable.ValueType = 1 THEN SUM(BookedAdult) + SUM(BookedChildren1) + SUM(BookedChildren2) + SUM(ActualAdult) + SUM(ActualChildren1) + SUM(ActualChildren2) ELSE 0 END AS NumberOfPaxRooms,
  CASE WHEN MISTable.ValueType = 1 AND MISTable.DateStamp = Reservations.ArrivalDate AND MISTable.ReservationStatus <= 6 THEN (CASE WHEN MISTAble.GuestNumber = 0 THEN SUM(MISTable.BookedNumberOfRooms)
   + SUM(MISTable.ActualNumberOfRooms) ELSE 1 END) ELSE 0 END AS NumberOfStaysRooms,
  CASE WHEN MISTable.ValueType = 1 AND MISTable.DateStamp = Reservations.ArrivalDate AND MISTable.ReservationStatus = 7 THEN (CASE WHEN MISTAble.GuestNumber = 0 THEN SUM(MISTable.BookedNumberOfRooms)
   + SUM(MISTable.ActualNumberOfRooms) ELSE 1 END) ELSE 0 END AS NumberOfCXLRooms,
  CASE WHEN MISTable.ValueType = 1 AND MISTable.DateStamp = Reservations.ArrivalDate AND MISTable.ReservationStatus = 8 THEN (CASE WHEN MISTAble.GuestNumber = 0 THEN SUM(MISTable.BookedNumberOfRooms)
   + SUM(MISTable.ActualNumberOfRooms) ELSE 1 END) ELSE 0 END AS NumberOfNoShowRooms,
  (CASE WHEN SUM(CASE WHEN MISTable.ValueType = 1 THEN ISNULL(BookedNumberOfRooms, 0) + ISNULL(ActualNumberOfRooms, 0) ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN MISTable.ValueType = 1 THEN ISNULL(BookedPrice, 0) + ISNULL(ActualPrice, 0) + ISNULL(ActualPackage, 0) ELSE 0 END) / SUM(CASE WHEN MISTable.ValueType = 1 THEN ISNULL(BookedNumberOfRooms, 0) + ISNULL(ActualNumberOfRooms, 0) ELSE 0 END) END) AS AVGTurnOverRooms, 
  CASE WHEN MISTable.ValueType = 3 THEN SUM(BookedPrice) + SUM(ActualPrice) + SUM(ActualPackage) ELSE 0 END AS TurnOverDayMeetings,
  CASE WHEN MISTable.ValueType = 3 AND MISTable.DateStamp = Reservations.ArrivalDate AND MISTable.ReservationStatus <= 6 AND (ISNULL(MISTable.ActualAdult, 0) + ISNULL(MISTable.BookedAdult, 0) > 0) THEN
   (CASE WHEN MISTAble.GuestNumber = 0 THEN SUM(MISTable.BookedNumberOfRooms) + SUM(MISTable.ActualNumberOfRooms) ELSE 1 END) ELSE 0 END AS NumberOfStaysDaymeetings,
  CASE WHEN MISTable.ValueType = 3 THEN SUM(BookedAdult) + SUM(BookedChildren1) + SUM(BookedChildren2) + SUM(ActualAdult) + SUM(ActualChildren1) + SUM(ActualChildren2) ELSE 0 END AS NumberOfPaxDaymeetings,
  CASE WHEN MISTable.ValueType = 2 THEN SUM(BookedPrice) + SUM(ActualPrice) + SUM(ActualPackage) ELSE 0 END AS TurnOverHalls,
  CASE WHEN MISTable.ValueType = 2 THEN SUM(BookedNumberOfRooms) + SUM(ActualNumberOfRooms) ELSE 0 END AS NumberOfHalls,
  CASE WHEN MISTable.ValueType = 2 THEN SUM(BookedAdult) + SUM(BookedChildren1) + SUM(BookedChildren2) + SUM(ActualAdult) + SUM(ActualChildren1) + SUM(ActualChildren2) ELSE 0 END AS NumberOfPaxHalls,
  CASE WHEN MISTable.ValueType = 4 THEN SUM(BookedPrice) + SUM(ActualPrice) ELSE 0 END AS TurnOverRestaurant,
  CASE WHEN MISTable.ValueType = 5 THEN SUM(BookedPrice) + SUM(ActualPrice) ELSE 0 END AS TurnOverOther,
  SUM(BookedPrice) + SUM(ActualPrice) + SUM(ActualPackage) AS TurnoverTotal
  INTO #CustomerOrderBook
  FROM MISTable WITH (NOLOCK)
  LEFT OUTER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTable.ReferenceNumber
    AND Reservations.GuestNumber = MISTable.GuestNumber
  LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
  LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
  LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = MISTable.FolioNumber
  LEFT OUTER JOIN ReservationAddress AddressFolio WITH (NOLOCK) ON AddressFolio.AddressNumber = Folios.ReservationAddressNumber
  INNER JOIN #CustomerLinks ON #CustomerLinks.CustomerNumber = ISNULL(ISNULL(ReservationAddress.Link, ReservationAddress.ContactPersonCustomerNumber), AddressFolio.Link)
  WHERE 1=1
  AND (ReservationAddress.Link IN (SELECT CustomerNumber FROM #CustomerLinks) OR ReservationAddress.ContactPersonCustomerNumber IN (SELECT CustomerNumber FROM #CustomerLinks) OR AddressFolio.Link IN (SELECT CustomerNumber FROM #CustomerLinks))
  AND MISTable.SegmentNumber IN (SELECT Value FROM SplitNumberList(@ASegments))
  AND MISTable.HotelCustomerNumber IN (SELECT CustomerNumber FROM #HotelNumbers)
  AND MISTable.ReservationStatus IN (SELECT Value FROM SplitNumberList(@AReservationStatuses))
  AND MISTable.DateStamp BETWEEN @ADateFrom AND @ADateTo 
  AND ISNULL(MISTable.CorrectionMoved, 0) IN (SELECT Value FROM SplitNumberList(@CorrectionStr))    
  AND ISNULL(MISTable.CorrectionDeleted, 0) IN (SELECT Value FROM SplitNumberList(@CorrectionStr))   
  GROUP BY ReservationAddress.Link, ReservationAddress.ContactPersonCustomerNumber, AddressFolio.Link, MISTable.SegmentNumber, MISTable.DateStamp, MISTable.HotelCustomerNumber, DATEPART(YEAR, DateStamp), DATEPART(MONTH, DateStamp), 
    Reservations.ArrivalDate, MISTable.ReservationStatus, MISTable.GuestNumber, MISTable.ActualAdult, MISTable.BookedAdult, MISTable.ValueType
END
ELSE IF @ASpecBy = 2  
BEGIN
  SELECT ISNULL(ISNULL(ReservationAddress.Link, ReservationAddress.ContactPersonCustomerNumber), AddressFolio.Link) AS CustomerNumber, MISTable.SegmentNumber, MISTable.DateStamp AS DateForValue, MISTable.HotelCustomerNumber, 
  DATEPART(YEAR, MISTable.DateStamp) AS Year, 
  DATEPART(WEEK, MISTable.DateStamp) AS WEEK,

  CASE WHEN MISTable.ValueType = 1 THEN SUM(BookedPrice) + SUM(ActualPrice) + SUM(ActualPackage) ELSE 0 END AS TurnoverRooms,
  CASE WHEN MISTable.ValueType = 1 THEN SUM(BookedNumberOfRooms) + SUM(ActualNumberOfRooms) ELSE 0 END AS NumberOfNightRooms,
  CASE WHEN MISTable.ValueType = 1 THEN SUM(BookedAdult) + SUM(BookedChildren1) + SUM(BookedChildren2) + SUM(ActualAdult) + SUM(ActualChildren1) + SUM(ActualChildren2) ELSE 0 END AS NumberOfPaxRooms,
  CASE WHEN MISTable.ValueType = 1 AND MISTable.DateStamp = Reservations.ArrivalDate AND MISTable.ReservationStatus <= 6 THEN (CASE WHEN MISTAble.GuestNumber = 0 THEN SUM(MISTable.BookedNumberOfRooms)
   + SUM(MISTable.ActualNumberOfRooms) ELSE 1 END) ELSE 0 END AS NumberOfStaysRooms,
  CASE WHEN MISTable.ValueType = 1 AND MISTable.DateStamp = Reservations.ArrivalDate AND MISTable.ReservationStatus = 7 THEN (CASE WHEN MISTAble.GuestNumber = 0 THEN SUM(MISTable.BookedNumberOfRooms)
   + SUM(MISTable.ActualNumberOfRooms) ELSE 1 END) ELSE 0 END AS NumberOfCXLRooms,
  CASE WHEN MISTable.ValueType = 1 AND MISTable.DateStamp = Reservations.ArrivalDate AND MISTable.ReservationStatus = 8 THEN (CASE WHEN MISTAble.GuestNumber = 0 THEN SUM(MISTable.BookedNumberOfRooms)
   + SUM(MISTable.ActualNumberOfRooms) ELSE 1 END) ELSE 0 END AS NumberOfNoShowRooms,
  (CASE WHEN SUM(CASE WHEN MISTable.ValueType = 1 THEN ISNULL(BookedNumberOfRooms, 0) + ISNULL(ActualNumberOfRooms, 0) ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN MISTable.ValueType = 1 THEN ISNULL(BookedPrice, 0) + ISNULL(ActualPrice, 0) + ISNULL(ActualPackage, 0) ELSE 0 END) / SUM(CASE WHEN MISTable.ValueType = 1 THEN ISNULL(BookedNumberOfRooms, 0) + ISNULL(ActualNumberOfRooms, 0) ELSE 0 END) END) AS AVGTurnOverRooms, 
  CASE WHEN MISTable.ValueType = 3 THEN SUM(BookedPrice) + SUM(ActualPrice) + SUM(ActualPackage) ELSE 0 END AS TurnOverDayMeetings,
  CASE WHEN MISTable.ValueType = 3 AND MISTable.DateStamp = Reservations.ArrivalDate AND MISTable.ReservationStatus <= 6 AND (ISNULL(MISTable.ActualAdult, 0) + ISNULL(MISTable.BookedAdult, 0) > 0) THEN
   (CASE WHEN MISTAble.GuestNumber = 0 THEN SUM(MISTable.BookedNumberOfRooms) + SUM(MISTable.ActualNumberOfRooms) ELSE 1 END) ELSE 0 END AS NumberOfStaysDaymeetings,
  CASE WHEN MISTable.ValueType = 3 THEN SUM(BookedAdult) + SUM(BookedChildren1) + SUM(BookedChildren2) + SUM(ActualAdult) + SUM(ActualChildren1) + SUM(ActualChildren2) ELSE 0 END AS NumberOfPaxDaymeetings,
  CASE WHEN MISTable.ValueType = 2 THEN SUM(BookedPrice) + SUM(ActualPrice) + SUM(ActualPackage) ELSE 0 END AS TurnOverHalls,
  CASE WHEN MISTable.ValueType = 2 THEN SUM(BookedNumberOfRooms) + SUM(ActualNumberOfRooms) ELSE 0 END AS NumberOfHalls,
  CASE WHEN MISTable.ValueType = 2 THEN SUM(BookedAdult) + SUM(BookedChildren1) + SUM(BookedChildren2) + SUM(ActualAdult) + SUM(ActualChildren1) + SUM(ActualChildren2) ELSE 0 END AS NumberOfPaxHalls,
  CASE WHEN MISTable.ValueType = 4 THEN SUM(BookedPrice) + SUM(ActualPrice) ELSE 0 END AS TurnOverRestaurant,
  CASE WHEN MISTable.ValueType = 5 THEN SUM(BookedPrice) + SUM(ActualPrice) ELSE 0 END AS TurnOverOther,
  SUM(BookedPrice) + SUM(ActualPrice) + SUM(ActualPackage) AS TurnoverTotal
  INTO #CustomerOrderBook2
  FROM MISTable WITH (NOLOCK)
  LEFT OUTER JOIN Reservations WITH (NOLOCK) ON Reservations.ReferenceNumber = MISTable.ReferenceNumber
    AND Reservations.GuestNumber = MISTable.GuestNumber
  LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = Reservations.ReferenceNumber
    AND ReservationAddressXTab.GuestNumber = Reservations.GuestNumber
  LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber
  LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = MISTable.FolioNumber
  LEFT OUTER JOIN ReservationAddress AddressFolio WITH (NOLOCK) ON AddressFolio.AddressNumber = Folios.ReservationAddressNumber
  INNER JOIN #CustomerLinks ON #CustomerLinks.CustomerNumber = ISNULL(ISNULL(ReservationAddress.Link, ReservationAddress.ContactPersonCustomerNumber), AddressFolio.Link)
  WHERE 1=1
  AND (ReservationAddress.Link IN (SELECT CustomerNumber FROM #CustomerLinks) OR ReservationAddress.ContactPersonCustomerNumber IN (SELECT CustomerNumber FROM #CustomerLinks) OR AddressFolio.Link IN (SELECT CustomerNumber FROM #CustomerLinks))
  AND MISTable.SegmentNumber IN (SELECT Value FROM SplitNumberList(@ASegments))
  AND MISTable.HotelCustomerNumber IN (SELECT CustomerNumber FROM #HotelNumbers)
  AND MISTable.ReservationStatus IN (SELECT Value FROM SplitNumberList(@AReservationStatuses))
  AND MISTable.DateStamp BETWEEN @ADateFrom AND @ADateTo 
  AND ISNULL(MISTable.CorrectionMoved, 0) IN (SELECT Value FROM SplitNumberList(@CorrectionStr))    
  AND ISNULL(MISTable.CorrectionDeleted, 0) IN (SELECT Value FROM SplitNumberList(@CorrectionStr))   
  GROUP BY ReservationAddress.Link, ReservationAddress.ContactPersonCustomerNumber, AddressFolio.Link, MISTable.SegmentNumber, MISTable.DateStamp, MISTable.HotelCustomerNumber, DATEPART(YEAR, DateStamp), DATEPART(WEEK, DateStamp), 
    Reservations.ArrivalDate, MISTable.ReservationStatus, MISTable.GuestNumber, MISTable.ActualAdult, MISTable.BookedAdult, MISTable.ValueType
END

DROP TABLE #HotelNumbers
DROP TABLE #CustomerLinks

/***** FILL IN THE BLANKS *****/

SET @TmpSegmentNumber = 0
SET @TmpYear = DATEPART(YEAR, @ADateFrom)
SET @TmpMonth = 1
SET @TmpWeek = 1
SET @FirstPass = 1

IF @ASpecBy < 2 
BEGIN
  DECLARE CurCustomerOrderBook CURSOR FAST_FORWARD FOR 
    SELECT SegmentNumber, Year, Month, DateForValue, HotelCustomerNumber 
    FROM #CustomerOrderBook WITH (NOLOCK) 
    ORDER BY SegmentNumber, Year, Month
  OPEN CurCustomerOrderBook

  FETCH NEXT FROM CurCustomerOrderBook INTO @CurSegmentNumber, @CurYear, @CurMonth, @CurDateForValue, @CurHotelCustomerNumber

  WHILE @@FETCH_STATUS<>-1
  BEGIN
    IF @TmpSegmentNumber <> @CurSegmentNumber 
    BEGIN
      -- Segment changed! 
      IF @FirstPass = 0
      BEGIN
        -- Fill in the missing last part of the previous segment 
        WHILE (@TmpYear <> DATEPART(YEAR, @ADateTo)) OR (@TmpMonth <> 12)
  	    BEGIN
          IF (@TmpMonth = 12)
          BEGIN
            SET @TmpYear = @TmpYear + 1
            SET @TmpMonth = 1
          END
          ELSE
            SET @TmpMonth = @TmpMonth + 1
	
          INSERT INTO #CustomerOrderBook VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpMonth, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
        END
      END

      SET @TmpSegmentNumber = @CurSegmentNumber
      SET @TmpYear = DATEPART(YEAR, @ADateFrom)
      SET @TmpMonth = 1
      SET @FirstPass = 1
    END

    -- Fill in the missing parts
    WHILE (@CurYear <> @TmpYear) OR (@CurMonth <> @TmpMonth)
    BEGIN
      IF @FirstPass = 1
        INSERT INTO #CustomerOrderBook VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpMonth, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

        IF (@TmpMonth = 12)
        BEGIN
          SET @TmpYear = @TmpYear + 1
          SET @TmpMonth = 1
        END
        ELSE
          SET @TmpMonth = @TmpMonth + 1

        IF (@FirstPass = 0) AND ((@CurYear <> @TmpYear) OR (@CurMonth <> @TmpMonth))
          INSERT INTO #CustomerOrderBook VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpMonth, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
    END

    SET @TmpSegmentNumber = @CurSegmentNumber
    SET @TmpYear = @CurYear
    SET @TmpMonth = @CurMonth  
    SET @FirstPass = 0
  
    FETCH NEXT FROM CurCustomerOrderBook INTO @CurSegmentNumber, @CurYear, @CurMonth, @CurDateForValue, @CurHotelCustomerNumber
  END

  CLOSE CurCustomerOrderBook
  DEALLOCATE CurCustomerOrderBook

  IF @TmpSegmentNumber <> 0
  BEGIN
    WHILE (@TmpYear <> DATEPART(YEAR, @ADateTo)) OR (@TmpMonth <> 12)
    BEGIN
      IF (@TmpMonth = 12)
      BEGIN
        SET @TmpYear = @TmpYear + 1
        SET @TmpMonth = 1
      END
      ELSE
        SET @TmpMonth = @TmpMonth + 1
	
      INSERT INTO #CustomerOrderBook VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpMonth, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
    END 
  END

  IF @ASpecBy = 1
  BEGIN
    DECLARE CurCustomerOrderBook CURSOR FAST_FORWARD FOR 
      SELECT CustomerNumber, SegmentNumber, Year, 
        SUM(TurnOverRooms + NumberOfNightRooms + NumberOfPaxRooms + NumberOfStaysRooms + NumberOfCXLRooms + NumberOfNoShowRooms + 
          TurnOverDayMeetings + NumberOfStaysDayMeetings + NumberOfPaxDayMeetings + TurnOverHalls + NumberOfHalls + NumberOfPaxHalls +
          TurnOverRestaurant + TurnOverOther) AS Total 
      FROM #CustomerOrderBook WITH (NOLOCK)
      GROUP BY CustomerNumber, SegmentNumber, Year
    OPEN CurCustomerOrderBook

    FETCH NEXT FROM CurCustomerOrderBook INTO @CustomerNumber, @SegmentNumber, @Year, @Total
    WHILE @@FETCH_STATUS <> -1
    BEGIN
      IF @Total = 0
        DELETE FROM #CustomerOrderBook WHERE CustomerNumber = @CustomerNumber AND SegmentNumber = @SegmentNumber AND Year = @Year
      FETCH NEXT FROM CurCustomerOrderBook INTO @CustomerNumber, @SegmentNumber, @Year, @Total
    END

    CLOSE CurCustomerOrderBook
    DEALLOCATE CurCustomerOrderBook
  END 
END
ELSE IF @ASpecBy = 2 
BEGIN
  DECLARE CurCustomerOrderBook2 CURSOR FAST_FORWARD FOR 
    SELECT SegmentNumber, Year, Week, DateForValue, HotelCustomerNumber 
    FROM #CustomerOrderBook2 WITH (NOLOCK) 
    ORDER BY SegmentNumber, Year, Week
  OPEN CurCustomerOrderBook2

  FETCH NEXT FROM CurCustomerOrderBook2 INTO @CurSegmentNumber, @CurYear, @CurWeek, @CurDateForValue, @CurHotelCustomerNumber

  WHILE @@FETCH_STATUS<>-1
  BEGIN
    IF @TmpSegmentNumber <> @CurSegmentNumber 
    BEGIN
      -- Segment changed! 
      IF @FirstPass = 0
      BEGIN
        -- Fill in the missing last part of the previous segment 
        WHILE (@TmpYear <> DATEPART(YEAR, @ADateTo)) OR (@TmpWeek <> 53)
  	    BEGIN
          IF (@TmpWeek = 53)
          BEGIN
            SET @TmpYear = @TmpYear + 1
            SET @TmpWeek = 1
          END
          ELSE
            SET @TmpWeek = @TmpWeek + 1
	
          INSERT INTO #CustomerOrderBook2 VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpWeek, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
  	    END
      END

      SET @TmpSegmentNumber = @CurSegmentNumber
      SET @TmpYear = DATEPART(YEAR, @ADateFrom)
      SET @TmpWeek = 1
      SET @FirstPass = 1
    END

  -- Fill in the missing parts
    WHILE (@CurYear <> @TmpYear) OR (@CurWeek <> @TmpWeek)
    BEGIN
      IF @FirstPass = 1
        INSERT INTO #CustomerOrderBook2 VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpWeek, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)

      IF (@TmpWeek = 53)
      BEGIN
        SET @TmpYear = @TmpYear + 1
        SET @TmpWeek = 1
      END
      ELSE
        SET @TmpWeek = @TmpWeek + 1

      IF (@FirstPass = 0) AND ((@CurYear <> @TmpYear) OR (@CurWeek <> @TmpWeek))
        INSERT INTO #CustomerOrderBook2 VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpWeek, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
    END

    SET @TmpSegmentNumber = @CurSegmentNumber
    SET @TmpYear = @CurYear
    SET @TmpWeek = @CurWeek  
    SET @FirstPass = 0
  
    FETCH NEXT FROM CurCustomerOrderBook2 INTO @CurSegmentNumber, @CurYear, @CurWeek, @CurDateForValue, @CurHotelCustomerNumber
  END

  CLOSE CurCustomerOrderBook2
  DEALLOCATE CurCustomerOrderBook2

  IF @TmpSegmentNumber <> 0
  BEGIN
    WHILE (@TmpYear <> DATEPART(YEAR, @ADateTo)) OR (@TmpWeek <> 53)
    BEGIN
      IF (@TmpWeek = 53)
      BEGIN
        SET @TmpYear = @TmpYear + 1
        SET @TmpWeek = 1
      END
      ELSE
        SET @TmpWeek = @TmpWeek + 1
	
      INSERT INTO #CustomerOrderBook2 VALUES (@ACustomerNumber, @TmpSegmentNumber, @CurDateForValue, @CurHotelCustomerNumber, @TmpYear, @TmpWeek, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
    END 
  END

  DECLARE CurCustomerOrderBook2 CURSOR FAST_FORWARD FOR 
    SELECT CustomerNumber, SegmentNumber, Year, 
      SUM(TurnOverRooms + NumberOfNightRooms + NumberOfPaxRooms + NumberOfStaysRooms + NumberOfCXLRooms + NumberOfNoShowRooms + 
        TurnOverDayMeetings + NumberOfStaysDayMeetings + NumberOfPaxDayMeetings + TurnOverHalls + NumberOfHalls + NumberOfPaxHalls +
        TurnOverRestaurant + TurnOverOther) AS Total 
    FROM #CustomerOrderBook2 WITH (NOLOCK)
    GROUP BY CustomerNumber, SegmentNumber, Year
  OPEN CurCustomerOrderBook2

  FETCH NEXT FROM CurCustomerOrderBook2 INTO @CustomerNumber, @SegmentNumber, @Year, @Total
  WHILE @@FETCH_STATUS <> -1
  BEGIN
    IF @Total = 0
      DELETE FROM #CustomerOrderBook2 WHERE CustomerNumber = @CustomerNumber AND SegmentNumber = @SegmentNumber AND Year = @Year
    FETCH NEXT FROM CurCustomerOrderBook2 INTO @CustomerNumber, @SegmentNumber, @Year, @Total
  END

  CLOSE CurCustomerOrderBook2
  DEALLOCATE CurCustomerOrderBook2
END

/***** SELECT AND ORDER THE SPECIFIED RESULT *****/
IF @ASpecBy < 2 
  SET @MemTableName = '#CustomerOrderBook'
ELSE IF @ASpecBy = 2 
  SET @MemTableName = '#CustomerOrderBook2'

SET @SelectStmt = 'SELECT '
IF @ASegments <> ''
BEGIN
  IF @ASpecBy < 2 
    SET @SelectStmt = @SelectStmt + '#CustomerOrderBook.SegmentNumber, Segments.Text AS SegmentName, '
  ELSE IF @ASpecBy = 2 
    SET @SelectStmt = @SelectStmt + '#CustomerOrderBook2.SegmentNumber, Segments.Text AS SegmentName, '
END

SET @SelectStmt = @SelectStmt + 'Year, '
IF @ASpecBy = 1
  SET @SelectStmt = @SelectStmt + 'Month, '
ELSE IF @ASpecBy = 2
  SET @SelectStmt = @SelectStmt + 'Week, '

SET @SelectStmt = @SelectStmt +
  'SUM(TurnOverRooms) AS TurnOverRooms, ' +
  'SUM(NumberOfNightRooms) AS NumberOfNightRooms, ' +
  'SUM(NumberOfPaxRooms) AS NumberOfPaxRooms, ' +
  'SUM(NumberOfStaysRooms) AS NumberOfStaysRooms, ' +
  'SUM(NumberOfCXLRooms) AS NumberOfCXLRooms, ' +
  'SUM(NumberOfNoShowRooms) AS NumberOfNoShowRooms, ' +
  '(CASE WHEN SUM(NumberOfNightRooms) = 0 THEN 0 ELSE SUM(TurnOverRooms) / SUM(NumberOfNightRooms) END) AS AVGTurnOverRooms, ' +
  'SUM(TurnOverDayMeetings) AS TurnOverDayMeetings, ' +
  'SUM(NumberOfStaysDayMeetings) AS NumberOfStaysDayMeetings, ' +
  'SUM(NumberOfPaxDayMeetings) AS NumberOfPaxDayMeetings, ' +
  'SUM(TurnOverHalls) AS TurnOverHalls, ' +
  'SUM(NumberOfHalls) AS NumberOfHalls, ' +
  'SUM(NumberOfPaxHalls) AS NumberOfPaxHalls, ' +
  'SUM(TurnOverRestaurant) AS TurnOverRestaurant, ' +
  'SUM(TurnOverOther) AS TurnOverOther, ' +
  'SUM(TurnOverTotal) AS TurnOverTotal , ' +
  '0 AS BonusPoints'

IF @ASpecBy < 2 
BEGIN
  SET @FromStmt = 'FROM #CustomerOrderBook WITH (NOLOCK) ' 
  IF @ASegments <> ''
    SET @FromStmt = @FromStmt + 'JOIN Segments WITH (NOLOCK) ON #CustomerOrderBook.SegmentNumber = Segments.Number'
END
ELSE IF @ASpecBy = 2 
BEGIN
  SET @FromStmt = 'FROM #CustomerOrderBook2 WITH (NOLOCK) ' 
  IF @ASegments <> ''
    SET @FromStmt = @FromStmt + 'JOIN Segments WITH (NOLOCK) ON #CustomerOrderBook2.SegmentNumber = Segments.Number'
END

SET @GroupByStmt = 'GROUP BY '
IF @ASegments <> ''
  SET @GroupByStmt = @GroupByStmt + 'SegmentNumber, Segments.Text, '
SET @GroupByStmt = @GroupByStmt + 'Year '

IF @ASpecBy = 1
  SET @GroupByStmt = @GroupByStmt + ', Month '
ELSE IF @ASpecBy = 2
  SET @GroupByStmt = @GroupByStmt + ', Week '

SET @OrderByStmt = 'ORDER BY '
IF @ASegments <> ''
BEGIN
  IF @ASpecBy < 2 
    SET @OrderByStmt = @OrderByStmt + '#CustomerOrderBook.SegmentNumber, '
  ELSE IF @ASpecBy = 2 
    SET @OrderByStmt = @OrderByStmt + '#CustomerOrderBook2.SegmentNumber, '
END

SET @OrderByStmt = @OrderByStmt + 'YEAR '
IF @AYearSort > 1
  SET @OrderByStmt = @OrderByStmt + 'DESC '

IF @ASpecBy = 1
  SET @OrderByStmt = @OrderByStmt + ', MONTH '
ELSE IF @ASpecBy = 2
  SET @OrderByStmt = @OrderByStmt + ', WEEK '

EXEC (@SelectStmt + ' ' + @FromStmt + ' ' + @WhereStmt + ' ' + @GroupByStmt + ' ' + @OrderByStmt)

IF @ASpecBy < 2 
  DROP TABLE #CustomerOrderBook
ELSE IF @ASpecBy = 2 
  DROP TABLE #CustomerOrderBook2