CREATE PROCEDURE [dbo].[SelectCustomerOrderBook] 
  @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 

/*
  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:
    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,
  @HotelStr VARCHAR(1000),
  @HotelNumber INT,
  @CorrectionStr VARCHAR(3),
  @HotelCustomerNumber VARCHAR(10),

  @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'
 
SET @HotelStr = @AHotels
CREATE Table #HotelNumbers(CustomerNumber INT NOT NULL)
CREATE Table #CustomerLinks(CustomerNumber INT NOT NULL)
INSERT INTO #CustomerLinks(CustomerNumber) EXEC FindOrderBookCustomerLinks @ACustomerNumber
INSERT INTO #CustomerLinks(CustomerNumber) VALUES (@ACustomerNumber)

IF @HotelStr <> ''
begin
  Declare @Pos INT

  SET @HotelStr = LTRIM(RTRIM(@HotelStr))+ ','
  SET @Pos = CHARINDEX(',', @HotelStr, 1)

  IF REPLACE(@HotelStr, ',', '') <> ''
  BEGIN
    WHILE @Pos > 0
    BEGIN
      SET @HotelCustomerNumber = LTRIM(RTRIM(LEFT(@HotelStr, @Pos - 1)))

      IF @HotelCustomerNumber <> ''
        INSERT INTO #HotelNumbers (CustomerNumber) VALUES (CAST(@HotelCustomerNumber AS INT)) --Use Appropriate conversion
      SET @HotelStr = RIGHT(@HotelStr, LEN(@HotelStr) - @Pos)
      SET @Pos = CHARINDEX(',', @HotelStr, 1)
    END
  END
end
else
begin
  Declare CustnumCurs CURSOR FAST_FORWARD FOR 
    SELECT convert(VARCHAR, Customer.CustomerNumber) FROM Customer WITH (NOLOCK) WHERE CustomerTypeNumber=7	
  Open CustnumCurs

  Fetch next from CustnumCurs INTO @HotelCustomerNumber

  while @@fetch_status <> -1
  begin
    INSERT INTO #HotelNumbers (CustomerNumber) VALUES (CAST(@HotelCustomerNumber AS INT))

    Fetch next from CustnumCurs INTO @HotelCustomerNumber
  end
  CLOSE CustnumCurs
  DEALLOCATE CustnumCurs
end

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

IF @ASpecBy < 2  
BEGIN
  SELECT 
    CustomerOrdreBook.CustomerNumber, 
    CustomerOrdreBook.SegmentNumber, 
    CustomerOrdreBook.DateForValue,
    CustomerOrdreBook.HotelCustomerNumber,
    DATEPART(YEAR, DateForValue) AS Year, 
    DATEPART(MONTH, DateForValue) AS Month,
    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(TurnOverRooms + TurnOverDayMeetings + TurnOverHalls + TurnOverRestaurant + TurnOverOther) AS TurnOverTotal
  INTO #CustomerOrderBook
  FROM CustomerOrdreBook WITH (NOLOCK) 
    JOIN Segments WITH (NOLOCK) ON Segments.Number = CustomerOrdreBook.SegmentNumber
    JOIN #CustomerLinks WITH (NOLOCK) ON #CustomerLinks.CustomerNumber IN 
     (CustomerOrdreBook.CustomerNumber, CustomerOrdreBook.ContactPersonCustomerNumber)  
  WHERE 
    CustomerOrdreBook.DateForValue BETWEEN @ADateFrom AND @ADateTo 
    AND CustomerOrdreBook.SegmentNumber IN (SELECT Value FROM SplitNumberList(@ASegments))     
    AND CustomerOrdreBook.HotelCustomerNumber IN (SELECT CustomerNumber FROM #HotelNumbers)
    AND CustomerOrdreBook.ReservationStatus IN (SELECT Value FROM SplitNumberList(@AReservationStatuses))
    AND ISNULL(CustomerOrdreBook.Correction, 0) IN (SELECT Value FROM SplitNumberList(@CorrectionStr))     
  GROUP BY 
    CustomerOrdreBook.SegmentNumber, 
    DATEPART(YEAR, DateForValue), 
    DATEPART(MONTH, DateForValue),
    CustomerOrdreBook.CustomerNumber,
    Segments.Text,
    DateForValue,
    HotelCustomerNumber
END
ELSE IF @ASpecBy = 2  
BEGIN

  SELECT 
    CustomerOrdreBook.CustomerNumber, 
    CustomerOrdreBook.SegmentNumber, 
    CustomerOrdreBook.DateForValue,
    CustomerOrdreBook.HotelCustomerNumber,
    DATEPART(YEAR, DateForValue) AS Year, 
    DATEPART(WEEK, DateForValue) AS Week,
    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(TurnOverRooms + TurnOverDayMeetings + TurnOverHalls + TurnOverRestaurant + TurnOverOther) AS TurnOverTotal
  INTO #CustomerOrderBook2
  FROM CustomerOrdreBook WITH (NOLOCK) 
    JOIN Segments WITH (NOLOCK) ON Segments.Number = CustomerOrdreBook.SegmentNumber
    JOIN #CustomerLinks WITH (NOLOCK) ON #CustomerLinks.CustomerNumber IN 
     (CustomerOrdreBook.CustomerNumber, CustomerOrdreBook.ContactPersonCustomerNumber)  
  WHERE 
    CustomerOrdreBook.DateForValue BETWEEN @ADateFrom AND @ADateTo 
    AND CustomerOrdreBook.SegmentNumber IN (SELECT Value FROM SplitNumberList(@ASegments))     
    AND CustomerOrdreBook.HotelCustomerNumber IN (SELECT CustomerNumber FROM #HotelNumbers)
    AND CustomerOrdreBook.ReservationStatus IN (SELECT Value FROM SplitNumberList(@AReservationStatuses))
    AND ISNULL(CustomerOrdreBook.Correction, 0) IN (SELECT Value FROM SplitNumberList(@CorrectionStr))     
  GROUP BY 
    CustomerOrdreBook.SegmentNumber, 
    DATEPART(YEAR, DateForValue), 
    DATEPART(WEEK, DateForValue),
    CustomerOrdreBook.CustomerNumber,
    Segments.Text,
    DateForValue,
    HotelCustomerNumber
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

  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

  CLOSE CurCustomerOrderBook
  DEALLOCATE CurCustomerOrderBook

  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

  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

  CLOSE CurCustomerOrderBook2
  DEALLOCATE CurCustomerOrderBook2

  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 '

--IF @ASpecBy = 2  PRINT 'Toeren'
EXEC (@SelectStmt + ' ' + @FromStmt + ' ' + @WhereStmt + ' ' + @GroupByStmt + ' ' + @OrderByStmt)

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



SET NOCOUNT OFF
