CREATE PROCEDURE [dbo].[MISSelect] @StartDate DATETIME, @EndDate DATETIME, 
  @DisplayType INT = 3, --reservations=0, Pieces=1, Persons=2, Turnover=3, ..., warecount=15 
  @DisplayResSubType INT = 0, --Reservations: 0 = Ordanary reservations, 1 = Only Main Course res., 2 = All res.
  @TypeGroupingValue INT, 
  @SelectCountryNumbers VARCHAR(MAX) = '',
  @SelectSegmentNumbers VARCHAR(1000) = '',
  @SelectPointOfSaleNumbers VARCHAR(1000) = '',
  @SelectRoomTypeNumbers VARCHAR(1000) = '',
  @SelectHallTypeNumbers VARCHAR(1000) = '',
  @MISValueTypes VARCHAR(20) = '1,2,3,4,5',
  @SelectHotelNumbers VARCHAR(500) = '',
  @SelectStatusNumbers VARCHAR(20) = '',
  @SelectStaffIDs VARCHAR(MAX) = '',
  @SelectPurposeCodeIds VARCHAR(1000) = '',
  @SelectCampaignCodeNumbers VARCHAR(MAX) = '',
  @SelectGuestTypeNumbers VARCHAR(1000) = '',
  @SelectProjectCodeNumbers VARCHAR(MAX) = '',
  @SelectProducts VARCHAR(MAX) = '',
  @SelectWares VARCHAR(MAX) = '',
  @SelectWeekDays VARCHAR(15) = '', --1: Monday...7: Sunday
  @OwnerType INT = 1, -- otNoOwner=0, otIncludeOwner=1, otOnlyOwner=2
  @AIncludeProvision BIT,
  @IncludeExtraSale BIT = 0, --manual sale last year
  @MinPaxPerRes INT = 0, 
  @MaxPaxPerRes INT = 0, 
  @SpecialMode INT = 0, -- 1: ADR, 2: REVPAR
  @IncludeAllotment INT = 0, 
  @HistYears INT = 1, --Years back (1, 2, 3)
  @ACustomerNumberCSV VARCHAR(1000) = ''
AS

/*Created 2018-10/SG
Selects content of MIS from MISTable (MIS) and MISHistory
Substitutes former SQL in Marketing (MISComponentsDBX) and adds MIS history
Calls function GetMISRoomCountInPeriod

KJ 20250212: Bug: Fix for "History not shown for reservation status".
KJ 20241214: Bug: Fix for "Group by Pax per room does not handle name list per person" (still issue with reservations where both name list per pax and per room is mixed - and most likely other issues).
KJ 20240820: Bug: Fix for "wrong number of rooms when selecting for single segments Case #CR247448"
KJ 20240528: Ext: MIS by CustomerNumber
KJ 20240425: Bug: Fix for "Number of reservations wrong. Only count on arrivaldate"
KJ 20240307: Bug: Fix for "Group by week sometimes shows booked numbers wrong if week 52 is both start and end of year"
KJ 20240202: Bug: Fix for "Invalid coolumn name ProjectCode". Case #CR240521
KJ 20231222: Bug: Fix for "When many types is selected then numbers does not show correct". Case #CR239183
KJ 20231218: Ext: Added Country to MisHistory
KJ 20231218: Bug: Fix for "History not correct for group by status, staff, campaign, country or ware"
KJ 20231215: Bug: Fix for "hist data not camparable if systemdate is after selected period."
KJ 20231214: Bug: Fix for "hist data not camparable if systemdate is before selected period." Case #CR237273
KJ 20231208: Bug: Fix for "Syntax error near '(' when using Pax per room"
2022-08-30: SG: History data 1, 2, or 3 years back
2022-07-19: SG: A "WhereStandard" section was missing
2022-05-31: SG: Corrected parameter @MISValueTypes, when it starts with a ,
2022-05-16: SG: Changed REVPAR-calculation
2022-05-06: KJ: Removed MISTableViewForMIS
2022-05-02, SG: Added @IncludeAllotment. Selects from MISTableViewForMIS instead of MISTableView
2022-01-18, SG: Added weekdays filter.
2021-11-10, SG: Added @TypeGroupingPaxPerRoom. Added PaxPerRoom in return records
2021-08-02, SG: Pieces/persons wrong for selected hotel in domino
2021-03-09, SG: WareCount changed to use BookedWareCount and ActualWareCount
2021-02-08, SG: Added support for ware count (@DisplayType=15), returned in ActualNumberOfRooms and BookedNumberOfRooms.
                Not supported in history booked data, because FK_WareNumber is not yet filled 1 year back.
20210205: KJ: Made some optimizations. Case #CR204629 + #CR204627
2020-01-19, SG: Added support for REVPAR. Added RoomCountInPeriod, RoomCountInHistPeriod, RoomCountBookedInHistPeriod, and RoomCountActualInHistPeriod in recordset. Calls function GetMISRoomCountInPeriod            
2021-01-18, SG: Corrected persons and pieces for res. with both rooms and halls and only one of the selected in MIS
2021-01-12, SG: Limited support of StaffID (only AutoDay, AutoNight) combined with pieces or pax (it has never worked until now)
2020-12-08, SG: Added params @MinPaxPerRes and @MaxPaxPerRes. Calculation of turnover of reservations within limits of PAX per reservation
2020-11-24, SG: Added @SQLStatement4, Last year, manual actual. 6 new columns inn result (Extra...)
2020-06-10, SG: Add ware. @TypeGroupingValue = 19 
2020-04-29, SG/KJ: Include provision added
2019-11-06, SG: Error when grouping on Roomtype or Status
2019-10-16, SG: If no hist. booking data, return next available hist. data date.
2019-06-06, SG: If no hist. booking data, select previous date (max. twice). Return SystemDateHistFromSP in recordset
2019-05-16, SG: Added param @DisplayResSubType (only for @DisplayType 0 )
2019-05-01, SG: BookedHist missing code for Hotel
2019-04-09, SG: Added param @DisplayType. Support @DisplayType = 0 (Reservations)
2019-01-18, SG: @TypeGroupingValue is different in 8.3 and local! (eg. Segment 8.3 = 5, Local = 6 etc.)
*/
SET NOCOUNT ON

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

DECLARE @LF CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @SQLStatement1 VARCHAR(MAX)
DECLARE @SQLStatementPaxPerRoomTmpTable VARCHAR(MAX)
DECLARE @SQLStatement1PiecesPax VARCHAR(MAX) = ''
DECLARE @SQLStatement1PiecesPaxActual VARCHAR(MAX) = ''
DECLARE @SQLRefIn VARCHAR(MAX)
DECLARE @SQLStatement2 VARCHAR(MAX) = ''--History, actual
DECLARE @SQLStatement2a VARCHAR(MAX) = ''--History, actual
DECLARE @SQLStatement3 VARCHAR(MAX) = ''--History, booked 
DECLARE @SQLStatement4 VARCHAR(MAX)--Last year, manual actual
  --@DisplayType: 7, 8, 9, 10: Turnover/pieces
  --           11, 12, 13, 14: Turnover/person
DECLARE @DisplayTypeRes INT = 0
DECLARE @DisplayTypePieces INT = 1
DECLARE @DisplayTypePersons INT = 2
DECLARE @DisplayTypeToPerPieces INT = 8
DECLARE @DisplayTypeToPerPersons INT = 9
DECLARE @DisplayTypeWareCount INT = 15
DECLARE @TypeGroupingDay INT = 1
DECLARE @TypeGroupingWeek INT = 2
DECLARE @TypeGroupingMonth INT = 3
DECLARE @TypeGroupingYear INT = 4
DECLARE @TypeGroupingHour INT = 5 --Not used yet
DECLARE @TypeGroupingSegment INT = 5 --8.3: 5
DECLARE @TypeGroupingPOS INT = 6 --8.3: 6
DECLARE @TypeGroupingRoomType INT = 7 --8.3: 7
DECLARE @TypeGroupingHallType INT = 8 --8.3: 8
DECLARE @TypeGroupingStatus INT = 9 --8.3: 9  
DECLARE @TypeGroupingStaff INT = 10 --8.3: 10 
DECLARE @TypeGroupingPurpose INT = 11 --8.3: 11
DECLARE @TypeGroupingCampaign INT = 12 --8.3: 12 
DECLARE @TypeGroupingGuestType INT = 13 --8.3: 13
DECLARE @TypeGroupingProject INT = 14 --8.3: 14 
DECLARE @TypeGroupingCountry INT = 15 --8.3: 15 
DECLARE @TypeGroupingHotel INT = 16 --8.3: 16
DECLARE @TypeGroupingProduct INT = 17 --8.3: 17
DECLARE @TypeGroupingWare INT = 19 --8.3: 19
DECLARE @TypeGroupingPaxPerRoom INT = 20 --8.3: 20

DECLARE @SQLTypeGrouping VARCHAR(MAX)
DECLARE @SQLTypeGroupingHist VARCHAR(MAX)
DECLARE @SQLDisplayTypeGrouping VARCHAR(MAX)
DECLARE @SQLTypeGroupingHistBook VARCHAR(MAX)
DECLARE @SQLDateStamp VARCHAR(50)
DECLARE @SQLDateStampHist VARCHAR(70)
DECLARE @SQLRoomCountInPeriod VARCHAR(MAX)--Only used for REVPAR
DECLARE @SQLRoomCountInHistPeriodBook VARCHAR(MAX)--Only used for REVPAR
DECLARE @SQLRoomCountInHistPeriodActual VARCHAR(MAX)--Only used for REVPAR
DECLARE @DateLimitField VARCHAR(20)
DECLARE @SQLSegment VARCHAR(50)
DECLARE @SQLSegmentHistBook VARCHAR(50)
DECLARE @SQLPOS VARCHAR(50)
DECLARE @SQLPOSHistBook VARCHAR(50)
DECLARE @SQLRoomType VARCHAR(50)
DECLARE @SQLValueType VARCHAR(20)
DECLARE @SQLHotelNumbers VARCHAR(50)
DECLARE @SQLHotelNumbersHistBook VARCHAR(50)
DECLARE @SQLStatus VARCHAR(50)
DECLARE @SQLStaff VARCHAR(50)
DECLARE @SQLPurpose VARCHAR(50)
DECLARE @SQLPurposeHistBook VARCHAR(50)
DECLARE @SQLCampaign VARCHAR(50)
DECLARE @SQLGuestType VARCHAR(50)
DECLARE @SQLGuestTypeHistBook VARCHAR(50)
DECLARE @SQLProject VARCHAR(50)
DECLARE @SQLCountry VARCHAR(MAX)
DECLARE @SQLProducts VARCHAR(MAX)
DECLARE @SQLProductsHistBook VARCHAR(MAX)
DECLARE @SQLWares VARCHAR(MAX)
DECLARE @SQLWaresHistBook VARCHAR(MAX)
DECLARE @OwnerTypeStr CHAR(1)
DECLARE @MainCourceResNotInclMIS_OrderBook BIT --Main course reservation not included in MIS and order book
DECLARE @MainCourceResNotInclMIS_OrderBookStr VARCHAR(100)
DECLARE @MainCourseJoin VARCHAR(200)
DECLARE @MainCourseWhere VARCHAR(50)
DECLARE @ProvisionWhere VARCHAR(100)
DECLARE @PaxPerResWhere VARCHAR(MAX)
DECLARE @FromJoin VARCHAR(MAX)
DECLARE @WhereStandard VARCHAR(MAX)
DECLARE @SQLActualPaxPieces VARCHAR(MAX)
DECLARE @SQLActualHistPaxPieces VARCHAR(MAX)
DECLARE @StaffCondition VARCHAR(MAX)
DECLARE @StaffConditionHist VARCHAR(MAX)
DECLARE @AllotmentWhere VARCHAR(100)
DECLARE @SQLStaffHistBook VARCHAR(50)
DECLARE @SQLCampaignHistBook VARCHAR(50)
DECLARE @SQLProjectHistBook VARCHAR(50)
DECLARE @SQLCountryHistBook VARCHAR(200)

CREATE TABLE #SelectCountryNumbers (No INT)
CREATE TABLE #SelectSegmentNumbers (No INT)
CREATE TABLE #SelectPointOfSaleNumbers (No INT)
CREATE TABLE #SelectRoomTypeNumbers (No INT)
CREATE TABLE #SelectHallTypeNumbers (No INT)
CREATE TABLE #MISValueTypes (No INT)
CREATE TABLE #SelectHotelNumbers (No INT)
CREATE TABLE #SelectHotelCustomerNumbers (No INT)
CREATE TABLE #SelectStatusNumbers (No INT)
CREATE TABLE #SelectStaffIDs (No INT)
CREATE TABLE #SelectPurposeCodeIds (No INT)
CREATE TABLE #SelectCampaignCodeNumbers (No INT)
CREATE TABLE #SelectGuestTypeNumbers (No INT)
CREATE TABLE #SelectProjectCodeNumbers (No INT)
CREATE TABLE #SelectProducts (No INT)
CREATE TABLE #SelectWares (No INT)
CREATE TABLE #SelectWeekDays (No INT)

--@ValueType may start with ,
IF LEFT(@MISValueTypes, 1) = ','
  SET @MISValueTypes = SUBSTRING(@MISValueTypes, 2, LEN(@MISValueTypes) - 1)

INSERT INTO #SelectCountryNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectCountryNumbers)

INSERT INTO #SelectSegmentNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectSegmentNumbers)

INSERT INTO #SelectPointOfSaleNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectPointOfSaleNumbers)

INSERT INTO #SelectRoomTypeNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectRoomTypeNumbers)

INSERT INTO #SelectHallTypeNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectHallTypeNumbers)

INSERT INTO #MISValueTypes
SELECT Value
FROM dbo.SplitListMax(@MISValueTypes)

INSERT INTO #SelectHotelNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectHotelNumbers)

INSERT INTO #SelectHotelCustomerNumbers
SELECT CustomerNumber
FROM customer c WITH (NOLOCK)
INNER JOIN #SelectHotelNumbers sh ON c.HotelNumber = sh.No

INSERT INTO #SelectStatusNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectStatusNumbers)

INSERT INTO #SelectStaffIDs
SELECT Value
FROM dbo.SplitListMax(@SelectStaffIDs)

INSERT INTO #SelectPurposeCodeIds
SELECT Value
FROM dbo.SplitListMax(@SelectPurposeCodeIds)

INSERT INTO #SelectCampaignCodeNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectCampaignCodeNumbers)

INSERT INTO #SelectGuestTypeNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectGuestTypeNumbers)

INSERT INTO #SelectProjectCodeNumbers
SELECT Value
FROM dbo.SplitListMax(@SelectProjectCodeNumbers)

INSERT INTO #SelectProducts
SELECT Value
FROM dbo.SplitListMax(@SelectProducts)

INSERT INTO #SelectWares
SELECT Value
FROM dbo.SplitListMax(@SelectWares)

INSERT INTO #SelectWeekDays
SELECT Value
FROM dbo.SplitListMax(@SelectWeekDays)

CREATE TABLE #CustLink (CustomerNumber INT NOT NULL)
INSERT INTO #CustLink
EXEC dbo.FindOrderBookCustomerLinksCSV @ACustomerNumberCSV

DECLARE @SystemDate DATE
DECLARE @MaxActualDate DATE
DECLARE @MaxBookedDate DATE
DECLARE @MinActualDate DATE
DECLARE @MinBookedDate DATE
DECLARE @CurrentDateStr CHAR(8)
DECLARE @SystemDateHist DATE
DECLARE @SystemDateHistFromSP DATE --To be returned in recordset

SELECT @CurrentDateStr = SYSTEMDATE FROM System WITH (NOLOCK) WHERE NUMBER = 1

SET @SystemDate = CONVERT(DATETIME, @CurrentDateStr)

--For REVPAR
IF @StartDate < @SystemDate
BEGIN
  SET @MinActualDate = @StartDate
  SET @MinBookedDate = @SystemDate

  IF @EndDate < @SystemDate
  BEGIN
    SET @MaxActualDate = @EndDate
    SET @MaxBookedDate = @StartDate --No Booked when negative
  END
  ELSE
  BEGIN
    SET @MaxActualDate = DATEADD(DAY, - 1, @SystemDate)
    SET @MaxBookedDate = @EndDate
  END
END
ELSE
BEGIN
  SET @MinActualDate = @StartDate
  SET @MaxActualDate = DATEADD(DAY, - 1, @StartDate) --No actual when negative 
  SET @MinBookedDate = @StartDate
  SET @MaxBookedDate = @EndDate
END

IF @ShowPrint = 1
BEGIN
  PRINT '@MinActualDate, @MaxActualDate, @MinBookedDate, @MaxBookedDate'
  PRINT @MinActualDate
  PRINT @MaxActualDate
  PRINT @MinBookedDate
  PRINT @MaxBookedDate
END

IF DATEPART(YEAR, @StartDate) < DATEPART(YEAR, @SystemDate)
  SET @SystemDateHist = DATEADD(yy, - (@HistYears + DATEPART(YEAR, @SystemDate) - DATEPART(YEAR, @StartDate)), @SystemDate)
ELSE
  SET @SystemDateHist = DATEADD(yy, -@HistYears, @SystemDate)

--If there is no historical data on @SystemDateHist, select previous day (max. twice)
DECLARE @noOfHistData INT
DECLARE @daysBack INT

SET @noOfHistData = 0
SET @daysBack = 0

WHILE @noOfHistData = 0 AND @daysBack <= 2
BEGIN
  SELECT @noOfHistData = count(*)
  FROM MISHistory WITH (NOLOCK)
  WHERE SystemDate = @SystemDateHist

  IF @noOfHistData = 0
  BEGIN
    SET @SystemDateHist = DATEADD(day, - @HistYears, @SystemDateHist)
    SET @daysBack = @daysBack + 1
  END
END

--Still no data? 
--Return 1. date (forward) having historic data. If no historic data at all, return a dummy-date: '2000-01-01'
IF @noOfHistData = 0
BEGIN
  DECLARE @Tmp DATE

  SELECT TOP 1 @Tmp = SystemDate
  FROM MISHistory
  WHERE SystemDate > @SystemDateHist
  ORDER BY SystemDate

  SET @SystemDateHistFromSP = ISNULL(@Tmp, '20000101')
END
ELSE
  SET @SystemDateHistFromSP = @SystemDateHist

IF @ShowPrint = 1
  PRINT '@SystemDateHist=' + CONVERT(VARCHAR, @SystemDateHist, 23) + ' @SystemDateHistFromSP=' + CONVERT(VARCHAR, @SystemDateHistFromSP, 23)

SELECT @MainCourceResNotInclMIS_OrderBookStr = KeyValue
FROM HotelSetup WITH (NOLOCK)
WHERE SearchKey = 'MainCourceResNotInclMIS_OrderBook'

SET @MainCourceResNotInclMIS_OrderBook = ISNULL(@MainCourceResNotInclMIS_OrderBookStr, 0)

CREATE TABLE #AutoUsers ([Number] [int] NOT NULL)

INSERT INTO #AutoUsers
  SELECT UserIdNumber
  FROM Personel WITH (NOLOCK)
  WHERE UserTypeNumber IN (3, 4)

SET @SQLStatementPaxPerRoomTmpTable = ''
SET @SQLTypeGrouping = ''
SET @SQLTypeGroupingHist = ''
SET @SQLTypeGroupingHistBook = ''
SET @SQLDisplayTypeGrouping = ''
SET @SQLDateStamp = '0 AS Datestamp, '
SET @SQLDateStampHist = '0 AS DateStamp, '
SET @SQLSegment = '0 AS SegmentNumber, '
SET @SQLSegmentHistBook = '0 AS SegmentNumber, '
SET @SQLPOS = '0 AS PointOfSaleNumber, '
SET @SQLPOSHistBook = '0 AS PointOfSaleNumber, '
SET @SQLRoomType = '0 AS RoomTypeNumber, '
SET @SQLHotelNumbers = '0 AS HotelCustomerNumber, '
SET @SQLHotelNumbersHistBook = '0 AS HotelCustomerNumber, '
SET @SQLStatus = '0 AS ReservationStatus, '
SET @SQLStaff = '0 AS StaffID, '
SET @SQLPurpose = '0 AS PurposeCode, '
SET @SQLPurposeHistBook = '0 AS PurposCode, '
SET @SQLCampaign = '0 AS CampaignCode, '
SET @SQLGuestType = '0 AS GuestType, '
SET @SQLGuestTypeHistBook = '0 AS GuestType, '
SET @SQLProject = '0 AS ProjectCode, '
SET @SQLCountry = '0 AS CountryNumber, '
SET @SQLProducts = '0 AS ProductNumber, '
SET @SQLProductsHistBook = '0 AS ProductNumber, '
SET @SQLWares = '0 AS WareNumber, '
SET @SQLWaresHistBook = '0 AS WareNumber, '
SET @SQLRoomCountInPeriod = '0 AS RoomCountActualInPeriod, 0 AS RoomCountBookedInPeriod, '
SET @SQLRoomCountInHistPeriodBook = '0 AS RoomCountBookedInHistPeriod, '
SET @SQLRoomCountInHistPeriodActual = '0 AS RoomCountActualInHistPeriod, '
SET @OwnerTypeStr = Convert(CHAR(1), @OwnerType)
SET @SQLStaffHistBook = '0 AS StaffId, '
SET @SQLCampaignHistBook = '0 AS CampaignCode, '
SET @SQLProjectHistBook = '0 AS ProjectCode, '
SET @SQLCountryHistBook = '0 AS CountryNumber, '

SET DATEFIRST 1 --mandag

IF @TypeGroupingValue = @TypeGroupingDay
BEGIN
  SET @SQLTypeGrouping = 'DateStamp '
  SET @SQLTypeGroupingHist = 'DateStamp ' 
  SET @SQLTypeGroupingHistBook = 'DateStamp '
  SET @SQLDateStamp = 'DateStamp, '
  SET @SQLDateStampHist = 'DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp) AS DateStamp, '

  IF @SpecialMode = 2 --RevPar
  BEGIN
    SET @SQLRoomCountInPeriod = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @MinActualDate, 112) + ''', ''' + CONVERT(CHAR(8), @MaxActualDate, 112) + ''', ''' + @SelectHotelNumbers + ''', 1, DateStamp, ''ACTUAL'') AS RoomCountActualInPeriod, ' + 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @MinBookedDate, 112) + ''', ''' + CONVERT(CHAR(8), @MaxBookedDate, 112) + ''', ''' + @SelectHotelNumbers + ''', 1, DateStamp, ''BOOK'') AS RoomCountBookedInPeriod, '
    SET @SQLRoomCountInHistPeriodActual = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), DATEADD(yy, - @HistYears, @StartDate), 112) + ''', ''' + CONVERT(CHAR(8), @SystemDateHist, 112) + ''', ''' + @SelectHotelNumbers + ''', 1, DateStamp, ''ACTUAL'') AS RoomCountActualInHistPeriod, '
    SET @SQLRoomCountInHistPeriodBook = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @SystemDateHist, 112) + ''', ''' + CONVERT(CHAR(8), DATEADD(yy, - @HistYears, @EndDate), 112) + ''', ''' + @SelectHotelNumbers + ''', 1, DateStamp, ''BOOK'') AS RoomCountBookedInHistPeriod, '
  END
END
ELSE IF @TypeGroupingValue = @TypeGroupingWeek
BEGIN
  SET @SQLTypeGrouping = 'DATEPART(WEEK, DateStamp), DATEPART(ISO_WEEK, DateStamp) '
  SET @SQLTypeGroupingHist = 'DATEPART(yy, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)), DATEPART(ISO_WEEK, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) '
  SET @SQLTypeGroupingHistBook = 'DATEPART(yy, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)), DATEPART(ISO_WEEK, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) '
  SET @SQLDateStamp = 'MIN(DateStamp) AS DateStamp, '
  SET @SQLDateStampHist = 'MIN(DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) AS DateStamp, '

  IF @SpecialMode = 2 --RevPar (WEEK understøttess ikke)
  BEGIN
    SET @SQLRoomCountInPeriod = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @MinActualDate, 112) + ''', ''' + CONVERT(CHAR(8), @MaxActualDate, 112) + ''', ''' + @SelectHotelNumbers + ''', 2, MIN(DateStamp), ''ACTUAL'') AS RoomCountActualInPeriod, ' + 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @MinBookedDate, 112) + ''', ''' + CONVERT(CHAR(8), @MaxBookedDate, 112) + ''', ''' + @SelectHotelNumbers + ''', 2, MIN(DateStamp), ''BOOK'') AS RoomCountBookedInPeriod, '
    SET @SQLRoomCountInHistPeriodActual = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), DATEADD(yy, - 1 * @HistYears, @StartDate), 112) + ''', ''' + CONVERT(CHAR(8), @SystemDateHist, 112) + ''', ''' + @SelectHotelNumbers + ''', 2, MIN(DateStamp), ''ACTUAL'') AS RoomCountActualInHistPeriod, '
    SET @SQLRoomCountInHistPeriodBook = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @SystemDateHist, 112) + ''', ''' + CONVERT(CHAR(8), DATEADD(yy, - @HistYears, @EndDate), 112) + ''', ''' + @SelectHotelNumbers + ''', 2, MIN(DateStamp), ''BOOK'') AS RoomCountBookedInHistPeriod, '
  END
END
ELSE IF @TypeGroupingValue = @TypeGroupingMonth
BEGIN
  SET @SQLTypeGrouping = 'DATEPART(yy, DateStamp), DATEPART(mm, DateStamp) '
  SET @SQLTypeGroupingHist = 'DATEPART(yy, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)), DATEPART(mm, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) '
  SET @SQLTypeGroupingHistBook = 'DATEPART(yy, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)), DATEPART(mm, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) '
  SET @SQLDateStamp = 'MIN(DateStamp) AS DateStamp, '
  SET @SQLDateStampHist = 'MIN(DATEADD(yy,' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) AS DateStamp, '

  IF @SpecialMode = 2 --RevPar
  BEGIN
    SET @SQLRoomCountInPeriod = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @MinActualDate, 112) + ''', ''' + CONVERT(CHAR(8), @MaxActualDate, 112) + ''', ''' + @SelectHotelNumbers + ''', 3, MIN(DateStamp), ''ACTUAL'') AS RoomCountActualInPeriod, ' + @LF + 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @MinBookedDate, 112) + ''', ''' + CONVERT(CHAR(8), @MaxBookedDate, 112) + ''', ''' + @SelectHotelNumbers + ''', 3, MIN(DateStamp), ''BOOK'') AS RoomCountBookedInPeriod, '
    SET @SQLRoomCountInHistPeriodActual = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), DATEADD(yy, - @HistYears, @StartDate), 112) + ''', ''' + CONVERT(CHAR(8), @SystemDateHist, 112) + ''', ''' + @SelectHotelNumbers + ''', 3, MIN(DateStamp), ''ACTUAL'') AS RoomCountActualInHistPeriod, ' + @LF
    SET @SQLRoomCountInHistPeriodBook = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @SystemDateHist, 112) + ''', ''' + CONVERT(CHAR(8), DATEADD(yy, - @HistYears, @EndDate), 112) + ''', ''' + @SelectHotelNumbers + ''', 3, MIN(DateStamp), ''BOOK'') AS RoomCountBookedInHistPeriod, ' + @LF
  END
END
ELSE IF @TypeGroupingValue = @TypeGroupingYear
BEGIN
  SET @SQLTypeGrouping = 'DATEPART(yy, DateStamp) '
  SET @SQLTypeGroupingHist = 'DATEPART(yy, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) '
  SET @SQLTypeGroupingHistBook = 'DATEPART(yy, DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) '
  SET @SQLDateStamp = 'MIN(DateStamp) AS DateStamp, '
  SET @SQLDateStampHist = 'MIN(DATEADD(yy, ' + CONVERT(CHAR(1), @HistYears) + ', DateStamp)) AS DateStamp, '

  IF @SpecialMode = 2 --RevPar
  BEGIN
    SET @SQLRoomCountInPeriod = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @MinActualDate, 112) + ''', ''' + CONVERT(CHAR(8), @MaxActualDate, 112) + ''', ''' + @SelectHotelNumbers + ''', 4, MIN(DateStamp), ''ACTUAL'') AS RoomCountActualInPeriod, ' + 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @MinBookedDate, 112) + ''', ''' + CONVERT(CHAR(8), @MaxBookedDate, 112) + ''', ''' + @SelectHotelNumbers + ''', 4, MIN(DateStamp), ''BOOK'') AS RoomCountBookedInPeriod, '
    SET @SQLRoomCountInHistPeriodActual = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), DATEADD(yy, - @HistYears, @StartDate), 112) + ''', ''' + CONVERT(CHAR(8), @SystemDateHist, 112) + ''', ''' + @SelectHotelNumbers + ''', 4, MIN(DateStamp), ''ACTUAL'') AS RoomCountActualInHistPeriod, '
    SET @SQLRoomCountInHistPeriodBook = 'dbo.GetMISRoomCountInPeriod(''' + CONVERT(CHAR(8), @SystemDateHist, 112) + ''', ''' + CONVERT(CHAR(8), DATEADD(yy, - @HistYears, @EndDate), 112) + ''', ''' + @SelectHotelNumbers + ''', 4, MIN(DateStamp), ''BOOK'') AS RoomCountBookedInHistPeriod, ' + @LF
  END
END 
ELSE IF @TypeGroupingValue = @TypeGroupingSegment
BEGIN
  SET @SQLTypeGrouping = 'MIS.SegmentNumber '
  SET @SQLTypeGroupingHist = 'MIS.SegmentNumber '
  SET @SQLTypeGroupingHistBook = 'FK_SegmentNumber '
  SET @SQLSegment = 'MIS.SegmentNumber, '
  SET @SQLSegmentHistBook = 'FK_SegmentNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingPOS
BEGIN
  SET @SQLTypeGrouping = 'MIS.PointOfSaleNumber '
  SET @SQLTypeGroupingHist = 'MIS.PointOfSaleNumber '
  SET @SQLTypeGroupingHistBook = 'FK_PointOfSaleNumber '
  SET @SQLPOS = 'MIS.PointOfSaleNumber, '
  SET @SQLPOSHistBook = 'FK_PointOfSaleNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingRoomType
BEGIN
  SET @SQLTypeGrouping = 'RoomTypeNumber '
  SET @SQLTypeGroupingHist = 'RoomTypeNumber '
  SET @SQLTypeGroupingHistBook = 'RoomTypeNumber '
  SET @SQLRoomType = 'RoomTypeNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingHallType
BEGIN
  SET @SQLTypeGrouping = 'RoomTypeNumber '
  SET @SQLTypeGroupingHist = 'RoomTypeNumber '
  SET @SQLTypeGroupingHistBook = 'RoomTypeNumber '
  SET @SQLRoomType = 'RoomTypeNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingStatus
BEGIN
  SET @SQLTypeGrouping = 'ReservationStatus '
  SET @SQLTypeGroupingHist = 'ReservationStatus '
  SET @SQLTypeGroupingHistBook = 'ReservationStatus '
  SET @SQLStatus = 'ReservationStatus, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingStaff
BEGIN
  SET @SQLTypeGrouping = 'StaffID '
  SET @SQLTypeGroupingHist = 'StaffID '
  SET @SQLTypeGroupingHistBook = 'FK_StaffId '
  SET @SQLStaff = 'StaffID, '
  SET @SQLStaffHistBook = 'FK_StaffId, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingPurpose
BEGIN
  SET @SQLTypeGrouping = 'PurposeCode '
  SET @SQLTypeGroupingHist = 'PurposeCode '
  SET @SQLTypeGroupingHistBook = 'FK_PurposeCodeNumber '
  SET @SQLPurpose = 'PurposeCode, '
  SET @SQLPurposeHistBook = 'FK_PurposeCodeNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingCampaign
BEGIN
  SET @SQLTypeGrouping = 'CampaignCode '
  SET @SQLTypeGroupingHist = 'CampaignCode '
  SET @SQLTypeGroupingHistBook = 'FK_CampaignCode '
  SET @SQLCampaign = 'CampaignCode, '
  SET @SQLCampaignHistBook = 'FK_CampaignCode, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingGuestType
BEGIN
  SET @SQLTypeGrouping = 'GuestType '
  SET @SQLTypeGroupingHist = 'GuestType '
  SET @SQLTypeGroupingHistBook = 'FK_GuestTypeNumber '
  SET @SQLGuestType = 'GuestType, '
  SET @SQLGuestTypeHistBook = 'FK_GuestTypeNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingProject
BEGIN
  SET @SQLTypeGrouping = 'ProjectCode '
  SET @SQLTypeGroupingHist = 'ProjectCode '
  SET @SQLTypeGroupingHistBook = 'FK_ProjectCode '
  SET @SQLProject = 'ProjectCode, '
  SET @SQLProjectHistBook = 'FK_ProjectCode, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingCountry
BEGIN
  SET @SQLTypeGrouping = 'ReservationAddress.CountryNumber, raf.CountryNumber, ReservationAddress.CountryCode '
  SET @SQLTypeGroupingHist = 'ReservationAddress.CountryNumber, raf.CountryNumber, ReservationAddress.CountryCode '
  SET @SQLTypeGroupingHistBook = 'FK_CountryNumber '
  SET @SQLCountry = 'CASE WHEN CASE WHEN ReservationAddress.CountryNumber IS NULL THEN raf.CountryNumber ELSE ReservationAddress.CountryNumber END IS NULL THEN ' + '(SELECT TOP 1 CountryNumber FROM Countries WITH (NOLOCK) WHERE Countries.CountryCode = ReservationAddress.CountryCode) ' + 'ELSE ' + 'CASE WHEN ReservationAddress.CountryNumber IS NULL THEN raf.CountryNumber ELSE ReservationAddress.CountryNumber END ' + 'END AS CountryNumber, '
  SET @SQLCountryHistBook = 'FK_CountryNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingHotel
BEGIN
  SET @SQLTypeGrouping = 'MIS.HotelCustomerNumber '
  SET @SQLTypeGroupingHist = 'MIS.HotelCustomerNumber '
  SET @SQLTypeGroupingHistBook = 'FK_HotelCustomerNumber '
  SET @SQLHotelNumbers = 'MIS.HotelCustomerNumber, '
  SET @SQLHotelNumbersHistBook = 'FK_HotelCustomerNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingProduct
BEGIN
  SET @SQLTypeGrouping = 'CASE WHEN MIS.ProductNumber = -1 THEN 0 ELSE IsNull(MIS.ProductNumber, 0) END '
  SET @SQLTypeGroupingHist = 'CASE WHEN MIS.ProductNumber = -1 THEN 0 ELSE IsNull(MIS.ProductNumber, 0) END '
  SET @SQLTypeGroupingHistBook = 'FK_ProdutNumber '
  SET @SQLProducts = 'CASE WHEN MIS.ProductNumber = -1 THEN 0 ELSE IsNull(MIS.ProductNumber, 0) END AS ProductNumber, '
  SET @SQLProductsHistBook = 'FK_ProdutNumber, '
END
ELSE IF @TypeGroupingValue = @TypeGroupingWare
BEGIN
  SET @SQLTypeGrouping = 'ISNULL(MIS.WareNumber, 0) '
  SET @SQLTypeGroupingHist = 'ISNULL(MIS.WareNumber, 0) '
  SET @SQLTypeGroupingHistBook = 'FK_WareNumber '
  SET @SQLWares = 'ISNULL(MIS.WareNumber, 0) AS WareNumber, '
  SET @SQLWaresHistBook = 'FK_WareNumber, '
END

IF @ShowPrint = 1
  PRINT '@DisplayType=' + CONVERT(VARCHAR, @DisplayType) + ', @TypeGroupingValue=' + CONVERT(VARCHAR, @TypeGroupingValue) + ', @SQLTypeGrouping=' + @SQLTypeGrouping + ', @SQLTypeGroupingHist=' + @SQLTypeGroupingHist

SET @MainCourseJoin = '';
SET @MainCourseWhere = '';

IF @DisplayType = @DisplayTypeRes --Reservation
BEGIN
  SET @SQLDisplayTypeGrouping = ', MIS.DateStamp '
  SET @DateLimitField = 'r.ArrivalDate';
  SET @SQLValueType = '0 AS ValueType, ';

  IF @DisplayResSubType = 0 --Only normal res.
  BEGIN
    SET @MainCourseJoin = 'LEFT OUTER JOIN ReservationReservationXtab x WITH (NOLOCK) ON r.ReferenceNumber = x.FK_ReferenceNumber ' + @LF
    SET @MainCourseWhere = 'AND x.FK_ReferenceNumber is null ' + @LF
  END
  ELSE IF @DisplayResSubType = 1 --Only main course res.
    SET @MainCourseJoin = 'INNER JOIN ReservationReservationXtab x WITH (NOLOCK) ON r.ReferenceNumber = x.FK_ReferenceNumber ' + @LF
  ELSE IF @DisplayResSubType = 2 --All res.
    SET @MainCourseJoin = ''
END
ELSE
BEGIN
  SET @DateLimitField = 'Datestamp';
  SET @SQLValueType = 'ValueType, ';
  SET @SQLDisplayTypeGrouping = ', ValueType'
END

SET @FromJoin = 'FROM MISTableView MIS WITH (NOLOCK) ' + @LF + 
  'LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = MIS.ReferenceNumber ' + @LF + 
  '  AND ReservationAddressXTab.GuestNumber = MIS.GuestNumber ' + @LF + 
  '  AND ReservationAddressXTab.AddressTypeNumber = 1  ' + @LF + 
  'LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber ' + @LF + 
  'LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = MIS.FolioNumber ' + @LF + 
  'LEFT OUTER JOIN ReservationAddress raf WITH (NOLOCK) ON raf.AddressNumber = Folios.ReservationAddressNumber ' + @LF + 
  'LEFT OUTER JOIN Reservations r WITH (NOLOCK) ON r.ReferenceNumber = MIS.ReferenceNumber and r.GuestNumber = MIS.GuestNumber ' + @LF + 
  @MainCourseJoin
IF ISNULL(@ACustomerNumberCSV, '') <> ''
BEGIN
  SET @FromJoin = @FromJoin + 
    'LEFT OUTER JOIN ReservationAddressXTab rax WITH (NOLOCK) ON rax.ReferenceNumber = MIS.ReferenceNumber ' + @LF +
    '  AND rax.GuestNumber = MIS.GuestNumber ' + @LF +
    'LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber '
END

IF @AIncludeProvision = 1
  SET @ProvisionWhere = '';
ELSE -- 0
  SET @ProvisionWhere = 'AND ISNULL(WareType, 0) NOT IN (10, 11, 12, 13, 14, 15, 16, 17, 18) '

IF @IncludeAllotment = 1
  SET @AllotmentWhere = 'AND Room_HallType IN (0, 1, 2, 3) '
ELSE
  SET @AllotmentWhere = 'AND Room_HallType IN (0, 1, 3) '

SET @PaxPerResWhere = ''

IF @DisplayType <> @TypeGroupingPaxPerRoom AND (ISNULL(@MinPaxPerRes, 0) <> 0 OR ISNULL(@MaxPaxPerRes, 0) <> 0) -- When display type = pax per room, then min/max pax is handled in Delphi
BEGIN
  IF ISNULL(@MaxPaxPerRes, 0) = 0
    SET @MaxPaxPerRes = 100000 --stor!

  SET @PaxPerResWhere = @LF + '/*Min/max pax*/' + @LF + 'AND MIS.ReferenceNumber IN (SELECT DISTINCT res.ReferenceNumber ' + @LF + --namelist
    'FROM Reservations res WITH (NOLOCK) ' + @LF + 
    'WHERE res.GuestNumber > 0 ' + @LF + 
    'AND res.Status NOT IN (7,8) ' + @LF + 
    'GROUP BY res.ReferenceNumber ' + @LF + 
    'HAVING SUM(ISNULL(res.Adults, 0) + ISNULL(res.Children1, 0) + ISNULL(res.Children2, 0)) BETWEEN ' + CONVERT(VARCHAR, @MinPaxPerRes) + ' AND ' + CONVERT(VARCHAR, @MaxPaxPerRes) + @LF + 

    'UNION ' + @LF + --Not namelist

    'SELECT DISTINCT res.ReferenceNumber ' + @LF + 
    'FROM Reservations res WITH (NOLOCK) ' + @LF + 
    'INNER JOIN RESERVATIONROOMLINEXTAB x WITH (NOLOCK) ON res.ReferenceNumber = x.ReferenceNumber AND x.GuestNumber = 0 ' + @LF + 
    'INNER JOIN RESERVATIONROOMLINE rl WITH (NOLOCK) ON x.LINENUMBER = rl.LINENUMBER ' + @LF + 
    'WHERE rl.StatusRL NOT IN (7,8) ' + @LF + 
    'GROUP BY res.ReferenceNumber ' + @LF + 
    'HAVING SUM(ISNULL(rl.Adults, 0) + ISNULL(rl.Children1, 0) + ISNULL(rl.Children2, 0)) BETWEEN ' + CONVERT(VARCHAR, @MinPaxPerRes) + ' AND ' + CONVERT(VARCHAR, @MaxPaxPerRes) + @LF + 

    'UNION ' + @LF + --Halls

    'SELECT DISTINCT rcl.ReferenceNumber ' + @LF + 
    'FROM ReservationConferenceLine rcl WITH (NOLOCK) ' + @LF + 
    'WHERE rcl.ReferenceNumber = r.ReferenceNumber ' + @LF + 
    'AND ISNULL(rcl.GuestNo, 0) = r.GuestNumber ' + @LF + 
    'AND rcl.Plenum = 1 ' + @LF + 
    'GROUP BY rcl.ReferenceNumber ' + @LF + 
    'HAVING SUM(ISNULL(rcl.Adults, 0) + ISNULL(rcl.Children1, 0) + ISNULL(rcl.Children2, 0)) BETWEEN ' + CONVERT(VARCHAR, @MinPaxPerRes) + ' AND ' + CONVERT(VARCHAR, @MaxPaxPerRes) + ') ' + @LF
END

--ADR: Staff-filter only valid back in time (transactions)
SET @StaffCondition = 'AND (''' + @SelectStaffIDs + ''' = '''' OR MIS.StaffID IN (SELECT No FROM #SelectStaffIDs) ' + 'OR (' + CONVERT(CHAR(2), @DisplayType) + ' = ' + CONVERT(CHAR(2), @DisplayTypeToPerPieces) + ' AND DateStamp >= ''' + CONVERT(VARCHAR, @SystemDate, 112) + ''')) ' + @LF
SET @StaffConditionHist = 'AND (''' + @SelectStaffIDs + ''' = '''' OR MIS.StaffID IN (SELECT No FROM #SelectStaffIDs) ' + 'OR (' + CONVERT(CHAR(2), @DisplayType) + ' = ' + CONVERT(CHAR(2), @DisplayTypeToPerPieces) + ' AND DateStamp >= ''' + CONVERT(VARCHAR, @SystemDateHist, 112) + ''')) ' + @LF

SET @WhereStandard = 'AND (''' + @SelectSegmentNumbers + ''' = '''' OR MIS.SegmentNumber IN (SELECT No FROM #SelectSegmentNumbers)) ' + @LF + 
  'AND (''' + @SelectPointOfSaleNumbers + ''' = '''' OR MIS.PointOfSaleNumber IN (SELECT No FROM #SelectPointOfSaleNumbers)) ' + @LF + 'AND ' + @LF + '   (''' + @SelectRoomTypeNumbers + ''' = '''' OR (MIS.RoomTypeNumber in (SELECT No FROM #SelectRoomTypeNumbers) AND ValueType = 1)) ' + @LF + --mvtRooms
  'AND (''' + @SelectHallTypeNumbers + ''' = '''' OR (MIS.RoomTypeNumber IN (SELECT No FROM #SelectHallTypeNumbers) AND ValueType in (2,3))) ' + @LF + --mvtHalls, mvtDaymeetings
  'AND (''' + @MISValueTypes + ''' = '''' OR ValueType IN (SELECT No FROM #MISValueTypes)) ' + @LF + 
  'AND (''' + @SelectHotelNumbers + ''' = '''' OR MIS.HotelCustomerNumber IN (SELECT No FROM #SelectHotelCustomerNumbers)) ' + @LF + 
  'AND (''' + @SelectStatusNumbers + ''' = '''' OR MIS.ReservationStatus IN (SELECT No FROM #SelectStatusNumbers)) ' + @LF + 
  'AND (''' + @SelectPurposeCodeIds + ''' = '''' OR r.PurposeCode IN (SELECT No FROM #SelectPurposeCodeIds)) ' + @LF + 
  'AND (''' + @SelectCampaignCodeNumbers + ''' = '''' OR MIS.CampaignCode IN (SELECT No FROM #SelectCampaignCodeNumbers)) ' + @LF + 
  'AND (''' + @SelectGuestTypeNumbers + ''' = '''' OR r.GuestType IN (SELECT No FROM #SelectGuestTypeNumbers)) ' + @LF + 
  'AND (''' + @SelectProjectCodeNumbers + ''' = '''' OR MIS.ProjectCode IN (SELECT No FROM #SelectProjectCodeNumbers)) ' + @LF + 
  'AND (''' + @SelectCountryNumbers + ''' = '''' OR CASE WHEN ReservationAddress.CountryNumber IS NULL THEN raf.CountryNumber ELSE ReservationAddress.CountryNumber END IN (SELECT No FROM #SelectCountryNumbers)) ' + @LF + 
  'AND (''' + @SelectProducts + ''' = '''' OR MIS.ProductNumber IN (SELECT No FROM #SelectProducts)) ' + @LF + 
  'AND (''' + @SelectWares + ''' = '''' OR MIS.WareNumber IN (SELECT No FROM #SelectWares)) ' + @LF + 
  'AND (''' + @SelectWeekDays + ''' = '''' OR DATEPART(WEEKDAY, MIS.DateStamp) IN (SELECT No FROM #SelectWeekDays)) ' + @LF + 
  'AND ((' + @OwnerTypeStr + ' = 0 AND OwnerReservation = 0) OR (' + @OwnerTypeStr + ' = 2 AND OwnerReservation = 1) OR (' + @OwnerTypeStr + ' = 1)) ' + @LF + -- otNoOwner=0, otIncludeOwner=1, otOnlyOwner=2 ' 
  @MainCourseWhere + @ProvisionWhere + @PaxPerResWhere + @LF + @AllotmentWhere + @LF
IF ISNULL(@ACustomerNumberCSV, '') <> ''
BEGIN
  SET @WhereStandard = @WhereStandard +
    'AND (ra.Link IN (SELECT CustomerNumber FROM #CustLink) OR ra.ContactPersonCustomerNumber IN (SELECT CustomerNumber FROM #CustLink) OR (MIS.StaffID NOT IN (SELECT Number FROM #AutoUsers) AND raf.Link IN (SELECT CustomerNumber FROM #CustLink))) ' + @LF
END

IF @TypeGroupingValue = @TypeGroupingStaff OR @DisplayType = @DisplayTypeWareCount
BEGIN --Normal
  SET @SQLActualPaxPieces = @LF + 'SUM(ActualAdult) AS ActualAdult, SUM(ActualChildren1) AS ActualChildren1, SUM(ActualChildren2) AS ActualChildren2, '

  IF @DisplayType = @DisplayTypeWareCount
    SET @SQLActualPaxPieces = @SQLActualPaxPieces + 'SUM(ActualWareCount) AS ActualNumberOfRooms, --ActualWareCount' + @LF
  ELSE
    SET @SQLActualPaxPieces = @SQLActualPaxPieces + 'SUM(ActualNumberOfRooms) AS ActualNumberOfRooms, '

  --Hist
  SET @SQLActualHistPaxPieces = @LF + 'SUM(MIS.ActualAdult) AS ActualHistAdult, SUM(MIS.ActualChildren1) AS ActualHistChildren1, SUM(MIS.ActualChildren2) AS ActualHistChildren2, '

  IF @DisplayType = @DisplayTypeWareCount
    SET @SQLActualHistPaxPieces = @SQLActualHistPaxPieces + 'SUM(ActualWareCount) AS ActualHistNumberOfRooms, --ActualWareCount' + @LF
  ELSE
    SET @SQLActualHistPaxPieces = @SQLActualHistPaxPieces + 'SUM(MIS.ActualNumberOfRooms) AS ActualHistNumberOfRooms, '
END
ELSE
BEGIN
  SET @SQLActualPaxPieces = @LF + '0 AS ActualAdult, 0 AS ActualChildren1, 0 AS ActualChildren2, 0 AS ActualNumberOfRooms, '
  SET @SQLActualHistPaxPieces = @LF + '0 AS ActualHistAdult, 0 AS ActualHistChildren1, 0 AS ActualHistChildren2, 0 AS ActualHistNumberOfRooms, '
END

IF @TypeGroupingValue = @TypeGroupingPaxPerRoom
BEGIN
  SET @SQLStatementPaxPerRoomTmpTable =
    'SELECT r.ReferenceNumber, Datestamp,' + @LF +
    'SUM(BookedAdult) AS BookedAdult, SUM(BookedChildren1) AS BookedChildren1, SUM(BookedChildren2) AS BookedChildren2, SUM(BookedNumberOfRooms) AS BookedNumberOfRooms' + @LF +
    'INTO #tmp' + @LF +
    @FromJoin + @LF + 
    'WHERE ValueType = 1 ' + @LF +  --Rooms
    'AND BookedAdult + BookedChildren1 + BookedChildren2 > 0 ' + @LF + 
    'AND ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, @EndDate, 112) + ''' ' + @LF + 
    'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, @StartDate, 112) + ''' ' + @LF + 
    @StaffCondition + @LF + 
    @WhereStandard + @LF + 
    'GROUP BY r.ReferenceNumber, Datestamp' + @LF + @LF +
    
    'SELECT r.ReferenceNumber, Datestamp,' + @LF +
    'SUM(ActualAdult) AS ActualAdult, SUM(ActualChildren1) AS ActualChildren1, SUM(ActualChildren2) AS ActualChildren2, SUM(ActualNumberOfRooms) AS ActualNumberOfRooms' + @LF +
    'INTO #tmp1' + @LF +
    @FromJoin +
    'WHERE ValueType = 1 ' + --Rooms
    'AND ActualAdult + ActualChildren1 + ActualChildren2 > 0 ' + @LF + 
    'AND ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, @EndDate, 112) + ''' ' + @LF + 
    'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, @StartDate, 112) + ''' ' + @LF + 
    @StaffCondition + @LF +
    @WhereStandard +
    'GROUP BY r.ReferenceNumber, Datestamp' + @LF + @LF +

    'SELECT r.ReferenceNumber, Datestamp,' + @LF +
    'SUM(MIS.ActualAdult) AS ActualHistAdult, SUM(MIS.ActualChildren1) AS ActualHistChildren1, SUM(MIS.ActualChildren2) AS ActualHistChildren2, SUM(MIS.ActualNumberOfRooms) AS ActualHistNumberOfRooms' + @LF +
    'INTO #tmp3' + @LF +
    @FromJoin + @LF + 
    'WHERE ActualAdult + ActualChildren1 + ActualChildren2 > 0 ' + @LF + 
    'AND ValueType = 1 ' + @LF + 
    'AND ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @EndDate), 112) + ''' ' + @LF +
    'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @StartDate), 112) + ''' ' + @LF + 
    'AND ' + @DateLimitField + ' < ''' + CONVERT(VARCHAR, @SystemDateHist, 112) + ''' ' + @LF + 
    @StaffConditionHist + @WhereStandard + @LF +
    'GROUP BY r.ReferenceNumber, Datestamp' + @LF + @LF +

    'SELECT MISHistory.FK_ReferenceNumber AS ReferenceNumber, DateStamp,' + @LF +
    'SUM(Adult) AS BookedHistAdult, SUM(Children1) AS BookedHistChildren1, SUM(Children2) AS BookedHistChildren2, SUM(NumberOfRooms) AS BookedHistNumberOfRooms' + @LF +
    'INTO #tmp4' + @LF +
    'FROM MISHistory WITH (NOLOCK) ' + @LF
    IF ISNULL(@ACustomerNumberCSV, '') <> ''
    BEGIN
      SET @SQLStatementPaxPerRoomTmpTable = @SQLStatementPaxPerRoomTmpTable +
        'LEFT OUTER JOIN ReservationAddressXTab rax WITH (NOLOCK) ON rax.ReferenceNumber = MISHistory.FK_ReferenceNumber ' + @LF +
        '  AND rax.GuestNumber = MISHistory.FK_GuestNumber ' + @LF +
        'LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber ' + @LF +
        'LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = MISHistory.FK_FolioNumber ' + @LF + 
        'LEFT OUTER JOIN ReservationAddress raf WITH (NOLOCK) ON raf.AddressNumber = Folios.ReservationAddressNumber ' + @LF
    END
    SET @SQLStatementPaxPerRoomTmpTable = @SQLStatementPaxPerRoomTmpTable +
      'WHERE Adult + Children1 + Children2 > 0 ' + @LF + 
      'AND ValueType = 1 ' + @LF + 
      'AND SystemDate = ''' + CONVERT(VARCHAR, @SystemDateHist, 23) + ''' ' + @LF + 
      'AND ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @EndDate), 23) + ''' ' + @LF +
      'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @StartDate), 23) + ''' ' + @LF + 
      'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, @SystemDateHist, 23) + ''' ' + @LF + 
      'AND (''' + @SelectSegmentNumbers + ''' = '''' OR FK_SegmentNumber IN (SELECT No FROM #SelectSegmentNumbers)) ' + @LF + 
      'AND (''' + @SelectPointOfSaleNumbers + ''' = '''' OR FK_PointOfSaleNumber IN (SELECT No FROM #SelectPointOfSaleNumbers)) ' + @LF +
      --Room- and hallType:
      'AND (''' + @SelectRoomTypeNumbers + ''' = '''' OR (RoomTypeNumber in (SELECT No FROM #SelectRoomTypeNumbers) AND ValueType = 1)) ' + @LF + --mvtRooms
      'AND (''' + @SelectHallTypeNumbers + ''' = '''' OR (RoomTypeNumber in (SELECT No FROM #SelectHallTypeNumbers) AND ValueType in (2,3))) ' + @LF + --mvtHalls, mvtDaymeetings
      'AND (''' + @MISValueTypes + ''' = '''' OR ValueType IN (SELECT No FROM #MISValueTypes)) ' + @LF + 
      'AND (''' + @SelectHotelNumbers + ''' = '''' OR FK_HotelCustomerNumber IN (SELECT No FROM #SelectHotelCustomerNumbers)) ' + @LF + 
      'AND (''' + @SelectPurposeCodeIds + ''' = '''' OR FK_PurposeCodeNumber IN (SELECT No FROM #SelectPurposeCodeIds)) ' + @LF + 
      'AND (''' + @SelectGuestTypeNumbers + ''' = '''' OR FK_GuestTypeNumber IN (SELECT No FROM #SelectGuestTypeNumbers)) ' + @LF + 
      'AND (''' + @SelectProducts + ''' = '''' OR FK_ProdutNumber IN (SELECT No FROM #SelectProducts)) ' + @LF + 
      'AND (''' + @SelectWares + ''' = '''' OR FK_WareNumber IN (SELECT No FROM #SelectWares)) ' + @LF + 
      'AND (''' + @SelectWeekDays + ''' = '''' OR DATEPART(WEEKDAY, DateStamp) IN (SELECT No FROM #SelectWeekDays)) ' + @LF + 
      'AND ''' + @SelectCountryNumbers + ''' = '''' ' + @LF +
      'AND ''' + @SelectStatusNumbers + ''' = '''' ' + @LF +
      'AND ''' + @SelectCampaignCodeNumbers + ''' = '''' ' + @LF + 
      'AND ''' + @SelectProjectCodeNumbers + ''' = '''' ' + @LF + 
      @AllotmentWhere + @LF
    IF ISNULL(@ACustomerNumberCSV, '') <> ''
    BEGIN
      SET @SQLStatementPaxPerRoomTmpTable = @SQLStatementPaxPerRoomTmpTable +
        'AND (ra.Link IN (SELECT CustomerNumber FROM #CustLink) OR ra.ContactPersonCustomerNumber IN (SELECT CustomerNumber FROM #CustLink) OR (MISHistory.FK_StaffId NOT IN (SELECT Number FROM #AutoUsers) AND raf.Link IN (SELECT CustomerNumber FROM #CustLink))) ' + @LF
    END
    SET @SQLStatementPaxPerRoomTmpTable = @SQLStatementPaxPerRoomTmpTable +
      'GROUP BY MISHistory.FK_ReferenceNumber, Datestamp' + @LF + @LF

 --Booked
  SET @SQLStatement1 =
    'SELECT 0 AS BookedReservations, 0 AS ActualReservations, ' + @LF + 
    '0 AS ActualPriceInclPackage, 0 AS ActualPriceInclPackageExVat, 0 AS ActualPriceExPackage, 0 AS ActualPriceExPackageExVat, 0 AS ActualOnlyPackage, 0 AS ActualOnlyPackageExVat, 0 AS ActualAdult, ' + @LF +
    '0 AS ActualChildren1, 0 AS ActualChildren2, 0 AS ActualNumberOfRooms, 0 AS BookedPriceInclPackage, 0 AS BookedPriceInclPackageExVat, 0 AS BookedPriceExPackage, 0 AS BookedPriceExPackageExVat, 0 AS BookedOnlyPackage, 0 AS BookedOnlyPackageExVat, ' + @LF + 
    'SUM(BookedAdult) AS BookedAdult, SUM(BookedChildren1) AS BookedChildren1, SUM(BookedChildren2) AS BookedChildren2, SUM(BookedNumberOfRooms) AS BookedNumberOfRooms, ' + @LF + 
    '0 AS ExtraPriceInclPackage, 0 AS ExtraPriceInclPackageExVat, 0 AS ExtraPriceExPackage, 0 AS ExtraPriceExPackageExVat, 0 AS ExtraOnlyPackage, 0 AS ExtraOnlyPackageExVat, ' + @LF + 
    @SQLDateStamp + @LF +
    @SQLSegment + @LF +
    @SQLPOS + @LF + 
    @SQLRoomType + '0 AS ValueType, ' + @LF + 
    @SQLHotelNumbers + @LF + 
    @SQLStatus + @LF + 
    @SQLStaff + @LF + 
    @SQLCampaign + @LF + 
    @SQLProject + @LF + 
    @SQLCountry + @LF + 
    @SQLGuestType + @LF + 
    @SQLPurpose + @LF + 
    @SQLProducts + @LF + 
    @SQLWares + @LF + 
    '(BookedAdult+BookedChildren1+BookedChildren2) / BookedNumberOfRooms AS PaxPerRoom,' + @LF +
    --History part
    '0 AS BookedHistReservations, 0 AS ActualHistReservations, 0 AS ActualHistAdult, 0 AS ActualHistChildren1, 0 AS ActualHistChildren2, 0 AS ActualHistNumberOfRooms, 0 AS ActualHistPriceInclPackage, 0 AS ActualHistPriceInclPackageExVat, 0 AS ActualHistPriceExPackage,' + @LF +
    '0 AS ActualHistPriceExPackageExVat, 0 AS ActualHistOnlyPackage, 0 AS ActualHistOnlyPackageExVat,' + @LF + 
    '0 AS BookedHistAdult, 0 AS BookedHistChildren1, 0 AS BookedHistChildren2, 0 AS BookedHistNumberOfRooms, 0 AS BookedHistPrice, 0 AS BookedHistVATOfPrice, 0 AS BookedHistPackage, 0 AS BookedHistVATOfPackage,' + @LF + 
    @SQLRoomCountInPeriod + @LF + 
    '0 AS RoomCountActualInHistPeriod, 0 AS RoomCountBookedInHistPeriod,' + @LF + 
    '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist' + @LF +
    'FROM #tmp WITH (NOLOCK)' + @LF +
    'GROUP BY (BookedAdult+BookedChildren1+BookedChildren2) / BookedNumberOfRooms' + @LF
    
  --Actual
  SET @SQLStatement1PiecesPaxActual = 
    'UNION ALL /*Union1*/' + @LF + @LF +

    'SELECT 0, 0,' + @LF + 
    '0, 0, 0, 0, 0, 0, SUM(ActualAdult) AS ActualAdult,' + @LF +
    'SUM(ActualChildren1) AS ActualChildren1, SUM(ActualChildren2) AS ActualChildren2, SUM(ActualNumberOfRooms) AS ActualNumberOfRooms, 0,0,0,0,0,0,' + @LF + 
    '0, 0, 0, 0,' + @LF +
    '0, 0, 0, 0, 0, 0,' + @LF +
    @SQLDateStamp + @LF +
    @SQLSegment + @LF + 
    @SQLPOS + @LF + 
    @SQLRoomType + '0 AS ValueType,' + @LF + 
    @SQLHotelNumbers + @LF + 
    @SQLStatus + @LF + 
    @SQLStaff + @LF + 
    @SQLCampaign + @LF + 
    @SQLProject + @LF + 
    @SQLCountry + @LF + 
    @SQLGuestType + @LF + 
    @SQLPurpose + @LF + 
    @SQLProducts + @LF + 
    @SQLWares + @LF + 
    '(ActualAdult + ActualChildren1 + ActualChildren2) / ActualNumberOfRooms AS PaxPerRoom, ' + @LF +
    --History part, 20 fields
    '0, 0, 0, 0, 0, 0, 0, 0, 0,' + @LF +
    '0, 0, 0,' + @LF +
    '0, 0, 0, 0, 0, 0, 0, 0,' + @LF + 
    @SQLRoomCountInPeriod + @LF + 
    '0 AS RoomCountActualInHistPeriod, 0 AS RoomCountBookedInHistPeriod,' + @LF + 
    '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist' + @LF +
    'FROM #tmp1 WITH (NOLOCK)' + @LF +
    'GROUP BY (ActualAdult+ActualChildren1+ActualChildren2) / ActualNumberOfRooms' + @LF 
END
ELSE
BEGIN
  SET @SQLStatement1 = 'SELECT ' + @LF

  IF @DisplayType = @DisplayTypeRes --Reservations
  BEGIN
    SET @SQLStatement1 = @SQLStatement1 + 'CASE WHEN MIS.DateStamp < ''' + CONVERT(VARCHAR, @SystemDate, 112) + ''' THEN 0 ELSE COUNT(DISTINCT (CHECKSUM(MIS.ReferenceNumber, ''/'', MIS.GuestNumber)) + CHECKSUM(REVERSE(MIS.ReferenceNumber),REVERSE(MIS.GuestNumber))) END AS BookedReservations, ' + @LF + 
      'CASE WHEN MIS.DateStamp >= ''' + CONVERT(VARCHAR, @SystemDate, 112) + ''' THEN 0 ELSE COUNT(DISTINCT (CHECKSUM(MIS.ReferenceNumber, ''/'', MIS.GuestNumber)) + CHECKSUM(REVERSE(MIS.ReferenceNumber),REVERSE(MIS.GuestNumber))) END AS ActualReservations, ' + @LF
  END
  ELSE
    SET @SQLStatement1 = @SQLStatement1 + '0 AS BookedReservations, ' + '0 AS ActualReservations, ' + @LF

  SET @SQLStatement1 = @SQLStatement1 + 'SUM(ActualPriceInclPackage) AS ActualPriceInclPackage, SUM(ActualPriceInclPackageExVat) AS ActualPriceInclPackageExVat, ' + @LF +
    'SUM(ActualPriceExPackage) AS ActualPriceExPackage, SUM(ActualPriceExPackageExVat) AS ActualPriceExPackageExVat, SUM(ActualOnlyPackage) AS ActualOnlyPackage, ' + @LF +
    'SUM(ActualOnlyPackageExVat) AS ActualOnlyPackageExVat, ' + @SQLActualPaxPieces + @LF + 
    'SUM(BookedPriceInclPackage) AS BookedPriceInclPackage, SUM(BookedPriceInclPackageExVat) AS BookedPriceInclPackageExVat, SUM(BookedPriceExPackage) AS BookedPriceExPackage, ' + @LF +
    'SUM(BookedPriceExPackageExVat) AS BookedPriceExPackageExVat, SUM(BookedOnlyPackage) AS BookedOnlyPackage, SUM(BookedOnlyPackageExVat) AS BookedOnlyPackageExVat, ' + @LF +
    'SUM(BookedAdult) AS BookedAdult, SUM(BookedChildren1) AS BookedChildren1, SUM(BookedChildren2) AS BookedChildren2, ' + @LF

  IF @DisplayType = @DisplayTypeWareCount
    SET @SQLStatement1 = @SQLStatement1 + 'SUM(BookedWareCount) AS BookedNumberOfRooms,' + @LF
  ELSE
    SET @SQLStatement1 = @SQLStatement1 + 'SUM(BookedNumberOfRooms) AS BookedNumberOfRooms, ' + @LF

  --Extra last year
  SET @SQLStatement1 = @SQLStatement1 + '0 AS ExtraPriceInclPackage, 0 AS ExtraPriceInclPackageExVat, 0 AS ExtraPriceExPackage, 0 AS ExtraPriceExPackageExVat, 0 AS ExtraOnlyPackage, 0 AS ExtraOnlyPackageExVat, ' + @LF +
    @SQLDateStamp + @LF + 
    @SQLSegment + @LF +
    @SQLPOS + @LF +
    @SQLRoomType + @LF +
    @SQLValueType + @LF +
    @SQLHotelNumbers + @LF +
    @SQLStatus + @LF +
    @SQLStaff + @LF +
    @SQLCampaign + @LF +
    @SQLProject + @LF +
    @SQLCountry + @LF +
    @SQLGuestType + @LF +
    @SQLPurpose + @LF +
    @SQLProducts + @LF + 
    @SQLWares + @LF +
    '0 AS PaxPerRoom, ' + @LF +
    --History part
    '0 AS BookedHistReservations, 0 AS ActualHistReservations, ' + @LF + 
    '0 AS ActualHistAdult, 0 AS ActualHistChildren1, 0 AS ActualHistChildren2, 0 AS ActualHistNumberOfRooms, 0 AS ActualHistPriceInclPackage, 0 AS ActualHistPriceInclPackageExVat, 0 AS ActualHistPriceExPackage, ' + @LF +
    '0 AS ActualHistPriceExPackageExVat, 0 AS ActualHistOnlyPackage, 0 AS ActualHistOnlyPackageExVat, ' + @LF +
    '0 AS BookedHistAdult, 0 AS BookedHistChildren1, 0 AS BookedHistChildren2, 0 AS BookedHistNumberOfRooms, 0 AS BookedHistPrice, 0 AS BookedHistVATOfPrice, 0 AS BookedHistPackage, 0 AS BookedHistVATOfPackage, ' + @LF +
    @SQLRoomCountInPeriod + @LF + 
    '0 AS RoomCountActualInHistPeriod, 0 AS RoomCountBookedInHistPeriod, ' + @LF + 
    '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist ' + @LF +
    @FromJoin + @LF + 
    'WHERE ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, @EndDate, 112) +''' ' + @LF +
    'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, @StartDate, 112) + ''' ' + @LF
  IF @DisplayType = @DisplayTypeRes --Reservations
  BEGIN
    SET @SQLStatement1 = @SQLStatement1 + 'AND r.ArrivalDate = MIS.DateStamp' + @LF + 
    'AND ISNULL(MIS.BookedNumberOfRooms, 0) + ISNULL(MIS.ActualNumberOfRooms, 0) > 0' + @LF
  END
  SET @SQLStatement1 = @SQLStatement1 + @StaffCondition + @LF +
    @WhereStandard + @LF +
    'GROUP BY ' + @SQLTypeGrouping + @SQLDisplayTypeGrouping --+ @SQLRefNumberGrouping
END

---------------------------------------------
--UNION with statement finding actual pieces and pax
--Only staff-filter allowed: AutoDay and AutoNight.
--Inner SELECT use standard WHERE-clause incl. Staff-filter to find ReferenceNumbers.
--Outer SELECT find pieces and pax originating from reservations. 
--Manual entered rooms has no reservation and does not contribute with any pieces or pax.
IF ((@DisplayType IN (1, 2, 7, 8, 9, 10, 11, 12, 13, 14)) OR @TypeGroupingValue = @TypeGroupingStaff) AND @TypeGroupingValue <> @TypeGroupingPaxPerRoom
BEGIN
  CREATE TABLE #Refs (Ref INT)

  SET @SQLRefIn = '  SELECT DISTINCT MIS.ReferenceNumber ' + @LF + 
    @FromJoin + @LF +
    '  WHERE 1=1 ' + @LF + 
    '  AND ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, @EndDate, 112) + ''' ' + @LF + 
    '  AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, @StartDate, 112) + ''' ' + @LF + 
    '  AND (''' + @MISValueTypes + ''' = '''' OR ValueType IN (SELECT No FROM #MISValueTypes)) ' + @LF + 
    @StaffCondition + 
    @WhereStandard + @LF

  INSERT INTO #Refs
  EXEC (@SQLRefIn)

  SET @SQLStatement1PiecesPax = @LF + 'UNION ALL /*Union2*/' + @LF + 
    'SELECT 0, 0, ' + @LF + 
    '0, 0, 0, 0, 0, 0, SUM(ActualAdult) AS ActualAdult, ' + @LF + 
    'SUM(ActualChildren1) AS ActualChildren1, SUM(ActualChildren2) AS ActualChildren2, SUM(ActualNumberOfRooms) AS ActualNumberOfRooms, 0, 0, 0, 0, 0, 0,' + @LF + 
    '0, 0, 0, 0, ' + @LF + --Booked
    '0, 0, 0, 0, 0, 0, ' + @LF + --Extra	
    @SQLDateStamp + @LF + 
    @SQLSegment + @LF +
    @SQLPOS + @LF +
    @SQLRoomType + @LF +
    @SQLValueType + @LF +
    @SQLHotelNumbers + @LF +
    @SQLStatus + @LF +
    @SQLStaff + @LF +
    @SQLCampaign + @LF +
    @SQLProject + @LF +
    @SQLCountry + @LF +
    @SQLGuestType + @LF +
    @SQLPurpose + @LF +
    @SQLProducts + @LF + 
    @SQLWares + @LF + 
    '0, ' + @LF + --PaxPerRoom
    -- History part
    '0, 0, ' + @LF + 
    '0, 0, 0, 0, 0, 0, 0, ' + @LF +
    '0, 0, 0, ' + @LF + 
    '0, 0, 0, 0, 0, 0, 0, 0, ' + @LF +
    '0, 0, 0, 0, ' + @LF + --RoomCount fields
    '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist ' + @LF + 
    @FromJoin + @LF + 
    'WHERE ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, @EndDate, 112) + ''' ' + @LF + 
    'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, @StartDate, 112) + ''' ' + @LF +
    --Part of "@WhereStandard"  
    'AND (''' + @SelectRoomTypeNumbers + ''' = '''' OR (MIS.RoomTypeNumber IN (SELECT No FROM #SelectRoomTypeNumbers) AND ValueType = 1)) ' + @LF + --mvtRooms
    'AND (''' + @SelectHallTypeNumbers + ''' = '''' OR (MIS.RoomTypeNumber IN (SELECT No FROM #SelectHallTypeNumbers) AND ValueType in (2,3))) ' + @LF + --mvtHalls, mvtDaymeetings
    'AND (''' + @MISValueTypes + ''' = '''' OR ValueType IN (SELECT No FROM #MISValueTypes)) ' + @LF + 
    'AND (''' + @SelectHotelNumbers + ''' = '''' OR MIS.HotelCustomerNumber IN (SELECT No FROM #SelectHotelCustomerNumbers)) ' + @LF +
    'AND (''' + @SelectProducts + ''' = '''' OR MIS.ProductNumber IN (SELECT No FROM #SelectProducts)) ' + @LF + 
    'AND (''' + @SelectWares + ''' = '''' OR MIS.WareNumber IN (SELECT No FROM #SelectWares)) ' + @LF + 
    'AND (''' + @SelectWeekDays + ''' = '''' OR DATEPART(WEEKDAY, MIS.DateStamp) IN (SELECT No FROM #SelectWeekDays)) ' + @LF +
    'AND MIS.ReferenceNumber IN (SELECT Ref FROM #Refs)' + @LF
  IF ISNULL(@ACustomerNumberCSV, '') <> ''
  BEGIN
    SET @SQLStatement1PiecesPax = @SQLStatement1PiecesPax +
      'AND (ra.Link IN (SELECT CustomerNumber FROM #CustLink) OR ra.ContactPersonCustomerNumber IN (SELECT CustomerNumber FROM #CustLink) OR (MIS.StaffID NOT IN (SELECT Number FROM #AutoUsers) AND raf.Link IN (SELECT CustomerNumber FROM #CustLink))) ' + @LF
  END
  SET @SQLStatement1PiecesPax = @SQLStatement1PiecesPax +
    'GROUP BY ' + @SQLTypeGrouping + @SQLDisplayTypeGrouping --+ @SQLRefNumberGrouping
END

--*****************
--History, actual
--*****************
SET @SQLStatement2a = ''
IF @TypeGroupingValue = @TypeGroupingPaxPerRoom
BEGIN
  SET @SQLStatement2 = @LF + 
    'UNION ALL /*Union3*/' + @LF + @LF +

    'SELECT 0 AS BookedReservations, 0 AS ActualReservations, ' + @LF +
    '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --20 felter
    --Extra last year
    '0, 0, 0, 0, 0, 0, ' + @LF + --6 felter 
    @SQLDateStampHist + @LF + 
    '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --14 felter (Segemnt - Wares)
    '(ActualHistAdult + ActualHistChildren1 + ActualHistChildren2) / ActualHistNumberOfRooms AS PaxPerRoom, ' + @LF + 
    '0, 0, SUM(ActualHistAdult) AS ActualHistAdult, SUM(ActualHistChildren1) AS ActualHistChildren1, SUM(ActualHistChildren2) AS ActualHistChildren2, SUM(ActualHistNumberOfRooms) AS ActualHistNumberOfRooms, ' + @LF + 
    '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,' + @LF + --18 felter
    '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist ' + @LF + 
    'FROM #tmp3' + @LF +
    'GROUP BY (ActualHistAdult + ActualHistChildren1 + ActualHistChildren2) / ActualHistNumberOfRooms'
END
ELSE
BEGIN
  SET @SQLStatement2 = @LF + 
    'UNION ALL /*Union4*/' + @LF + 
    'SELECT 0 AS BookedReservations, ' + '0 AS ActualReservations, ' + @LF + 
    '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --20 felter
    --Extra last year
    '0, 0, 0, 0, 0, 0, ' + @LF + --6 felter 
    @SQLDateStampHist + @LF + 
    @SQLSegment + @LF +
    @SQLPOS + @LF +
    @SQLRoomType + @LF +
    @SQLValueType + @LF +
    @SQLHotelNumbers + @LF +
    @SQLStatus + @LF +
    @SQLStaff + @LF +
    @SQLCampaign + @LF +
    @SQLProject + @LF +
    @SQLCountry + @LF +
    @SQLGuestType + @LF +
    @SQLPurpose + @LF +
    @SQLProducts + @LF +
    @SQLWares + '0 AS PaxPerRoom, '
  --Actual history part
  IF @DisplayType = @DisplayTypeRes --Reservations
    SET @SQLStatement2 = @SQLStatement2 + 'CASE WHEN MIS.DateStamp < ''' + CONVERT(VARCHAR, @SystemDateHist, 112) + ''' THEN 0 ELSE COUNT(DISTINCT (CHECKSUM(MIS.ReferenceNumber, ''/'', MIS.GuestNumber)) + CHECKSUM(REVERSE(MIS.ReferenceNumber),REVERSE(MIS.GuestNumber))) END AS BookedHistReservations, ' + @LF + 
      'CASE WHEN MIS.DateStamp >= ''' + CONVERT(VARCHAR, @SystemDateHist, 112) + ''' THEN 0 ELSE COUNT(DISTINCT (CHECKSUM(MIS.ReferenceNumber, ''/'', MIS.GuestNumber)) + CHECKSUM(REVERSE(MIS.ReferenceNumber),REVERSE(MIS.GuestNumber))) END AS ActualHistReservations, ' + @LF
  ELSE
    SET @SQLStatement2 = @SQLStatement2 + '0 AS BookedHistReservations, ' + '0 AS ActualHistReservations, ' + @LF

  SET @SQLStatement2 = @SQLStatement2 + @SQLActualHistPaxPieces + @LF +
    'SUM(ActualPriceInclPackage) AS ActualHistPriceInclPackage, SUM(ActualPriceInclPackageExVat) AS ActualHistPriceInclPackageExVat, SUM(ActualPriceExPackage) AS ActualHistPriceExPackage, ' + @LF + 
    'SUM(ActualPriceExPackageExVat) AS ActualHistPriceExPackageExVat, SUM(ActualOnlyPackage) AS ActualHistOnlyPackage, SUM(ActualOnlyPackageExVat) AS ActualHistOnlyPackageExVat, 0 AS BookedHistAdult, ' + @LF +
    '0 AS BookedHistChildren1, 0 AS BookedHistChildren2, 0 AS BookedHistNumberOfRooms, 0 AS BookedHistPrice, 0 AS BookedHistVATOfPrice, 0 AS BookedHistPackage, 0 AS BookedHistVATOfPackage, ' + @LF + 
    '0, 0, ' + @LF + --RoomCount fields
    @SQLRoomCountInHistPeriodActual + '0, ' + '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist ' + @LF + 
    'FROM MISTableView MIS WITH (NOLOCK) ' + @LF + 
    'LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = MIS.ReferenceNumber ' + @LF + 
    '  AND ReservationAddressXTab.GuestNumber = MIS.GuestNumber ' + @LF + 
    '  AND ReservationAddressXTab.AddressTypeNumber = 1  ' + @LF + 
    'LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber  ' + @LF + 
    'LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = MIS.FolioNumber  ' + @LF + 
    'LEFT OUTER JOIN ReservationAddress raf WITH (NOLOCK) ON raf.AddressNumber = Folios.ReservationAddressNumber  ' + @LF + 
    'LEFT OUTER JOIN Reservations r WITH (NOLOCK) on r.ReferenceNumber = MIS.ReferenceNumber AND r.GuestNumber = MIS.GuestNumber ' + @LF
  IF ISNULL(@ACustomerNumberCSV, '') <> ''
  BEGIN
    SET @SQLStatement2 = @SQLStatement2 +
      'LEFT OUTER JOIN ReservationAddressXTab rax WITH (NOLOCK) ON rax.ReferenceNumber = MIS.ReferenceNumber ' + @LF +
      '  AND rax.GuestNumber = MIS.GuestNumber ' + @LF +
      'LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber ' + @LF
  END
  SET @SQLStatement2 = @SQLStatement2 +
    @MainCourseJoin + 
    'WHERE ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @EndDate), 112) + ''' ' +
    'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @StartDate), 112) + ''' ' + @LF + 
    'AND ' + @DateLimitField + ' < ''' + CONVERT(VARCHAR, @SystemDateHist, 112) + ''' ' + @LF
  IF @DisplayType = @DisplayTypeRes --Reservations
  BEGIN
    SET @SQLStatement2 = @SQLStatement2 + 'AND r.ArrivalDate = MIS.DateStamp' + @LF +
    'AND ISNULL(MIS.BookedNumberOfRooms, 0) + ISNULL(MIS.ActualNumberOfRooms, 0) > 0' + @LF
  END

  SET @SQLStatement2 = @SQLStatement2 + @StaffConditionHist + @LF +
    @WhereStandard + @LF + 
    'GROUP BY ' + @SQLTypeGroupingHist + @SQLDisplayTypeGrouping
  ---------------------------------------------
  --UNION with statement finding pieces and pax
  IF @DisplayType IN (@DisplayTypePieces, @DisplayTypePersons, @DisplayTypeToPerPieces, @DisplayTypeToPerPersons) OR @TypeGroupingValue = @TypeGroupingStaff
  BEGIN
    SET @SQLStatement2a = @LF + @LF + 
      'UNION ALL /*Union5*/' + @LF + 
      
      'SELECT 0, 0, ' + @LF + 
      '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --20 felter
      '0,0,0,0,0,0, ' + @LF + --Extra	
      @SQLDateStampHist + @LF + 
      @SQLSegment + @LF + 
      @SQLPOS + @LF + 
      @SQLRoomType + @LF + 
      @SQLValueType + @LF + 
      @SQLHotelNumbers + @LF + 
      @SQLStatus + @LF + 
      @SQLStaff + @LF + 
      @SQLCampaign + @LF + 
      @SQLProject + @LF + 
      @SQLCountry + @LF + 
      @SQLGuestType + @LF + 
      @SQLPurpose + @LF + 
      @SQLProducts + @LF + 
      @SQLWares + @LF + 
      '0, ' + @LF + --PaxPerRoom
      --Actual history part
      '0 AS BookedHistReservations, 0 AS ActualHistReservations, ' + @LF + 
      'SUM(MIS.ActualAdult) AS ActualHistAdult, SUM(MIS.ActualChildren1) AS ActualHistChildren1, SUM(MIS.ActualChildren2) AS ActualHistChildren2, SUM(MIS.ActualNumberOfRooms) AS ActualHistNumberOfRooms, 0,0,0,0,0,0, ' + @LF + --Actual turnovers
      '0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --Booked history
      '0, 0, 0, 0, ' + @LF + --RoomCount fields
      '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist ' + @LF + 
      @FromJoin + @LF +
      'WHERE ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @EndDate), 112) + ''' ' + @LF +
      'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @StartDate), 112) + ''' ' + @LF + 
      'AND ' + @DateLimitField + ' < ''' + CONVERT(VARCHAR, @SystemDateHist, 112) + ''' ' + @LF + @WhereStandard + 
      'AND MIS.ReferenceNumber IN (' + @LF + --from transactions
      '  SELECT distinct MIS.ReferenceNumber ' + @LF + 
      '  ' + @FromJoin + @LF + 
      'WHERE ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @EndDate), 112) + ''' ' + @LF +
      'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @StartDate), 112) + ''' ' + @LF + 
      'AND ' + @DateLimitField + ' < ''' + CONVERT(VARCHAR, @SystemDateHist, 112) + ''' ' + @LF + 
      @StaffConditionHist + @LF + 
      @WhereStandard + ') ' + @LF + 
      'GROUP BY ' + @SQLTypeGrouping + @SQLDisplayTypeGrouping --+ @SQLRefNumberGrouping
  END
END

IF @IncludeExtraSale = 1
BEGIN
  --*****************
  --Extra @HistYears years back
  --*****************
  SET @SQLstatement4 = 'SELECT 0 AS BookedReservations, 0 AS ActualReservations, ' + @LF + 
    '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --20 felter
    --Extra last year
    'SUM(ActualPriceInclPackage) AS ExtraPriceInclPackage, SUM(ActualPriceInclPackageExVat) AS ExtraPriceInclPackageExVat, SUM(ActualPriceExPackage) AS ExtraPriceExPackage, SUM(ActualPriceExPackageExVat) AS ExtraPriceExPackageExVat, ' + @LF +
    'SUM(ActualOnlyPackage) AS ExtraOnlyPackage, SUM(ActualOnlyPackageExVat) AS ExtraOnlyPackageExVat, ' + @SQLDateStampHist + @LF + 
    @SQLSegment + @LF +
    @SQLPOS + @LF +
    @SQLRoomType + @LF +
    @SQLValueType + @LF +
    @SQLHotelNumbers + @LF +
    @SQLStatus + @LF +
    @SQLStaff + @LF +
    @SQLCampaign + @LF +
    @SQLProject + @LF +
    @SQLCountry + @LF +
    @SQLGuestType + @LF +
    @SQLPurpose + @LF +
    @SQLProducts + @LF +
    @SQLWares + @LF +
    '0, ' + @LF + --PaxPerRoom
    --Hist, 20 felter
    '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + 
    '0, 0, 0, 0, ' + @LF + --RoomCount fields
    '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist ' + @LF + 
    'FROM MISTableView MIS WITH (NOLOCK) ' + @LF + 
    'LEFT OUTER JOIN ReservationAddressXTab WITH (NOLOCK) ON ReservationAddressXTab.ReferenceNumber = MIS.ReferenceNumber ' + @LF + 
    '   AND ReservationAddressXTab.GuestNumber = MIS.GuestNumber ' + @LF +
    '   AND ReservationAddressXTab.AddressTypeNumber = 1 ' + @LF + 
    'LEFT OUTER JOIN ReservationAddress WITH (NOLOCK) ON ReservationAddress.AddressNumber = ReservationAddressXTab.AddressNumber ' + @LF + 
    'LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = MIS.FolioNumber  ' + @LF + 
    'LEFT OUTER JOIN ReservationAddress raf WITH (NOLOCK) ON raf.AddressNumber = Folios.ReservationAddressNumber  ' + @LF + 
    'LEFT OUTER JOIN Reservations r WITH (NOLOCK) on MIS.ReferenceNumber = r.ReferenceNumber AND r.GuestNumber = 0 ' + @LF + 
    @MainCourseJoin + @LF + 
    'WHERE StaffId NOT IN (SELECT * FROM #AutoUsers WITH (NOLOCK)) ' + @LF + 
    'AND ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @EndDate), 112) + ''' ' + @LF + 
    'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @StartDate), 112) + ''' ' + @LF + 
    'AND ' + @DateLimitField + ' > ''' + CONVERT(VARCHAR, @SystemDateHist, 112) + ''' ' + @LF + 
    @WhereStandard + @LF + 
    'GROUP BY ' + @SQLTypeGroupingHist + @SQLDisplayTypeGrouping
END

IF @ShowPrint = 1
BEGIN
  PRINT ''
  PRINT ''
  PRINT '----------------------------                    manuel run start here                 ---------------------------------'
  PRINT ''
  PRINT ''
  PRINT 'CREATE TABLE #SelectCountryNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectSegmentNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectPointOfSaleNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectRoomTypeNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectHallTypeNumbers (No INT)'
  PRINT 'CREATE TABLE #MISValueTypes (No INT)'
  PRINT 'CREATE TABLE #SelectHotelNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectHotelCustomerNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectStatusNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectStaffIDs (No INT)'
  PRINT 'CREATE TABLE #SelectPurposeCodeIds (No INT)'
  PRINT 'CREATE TABLE #SelectCampaignCodeNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectGuestTypeNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectProjectCodeNumbers (No INT)'
  PRINT 'CREATE TABLE #SelectProducts (No INT)'
  PRINT 'CREATE TABLE #SelectWares (No INT)'
  PRINT 'CREATE TABLE #SelectWeekDays (No INT)'
  PRINT 'CREATE TABLE #Refs (Ref INT)'
  PRINT ''
  PRINT 'CREATE TABLE #CustLink (CustomerNumber INT NOT NULL)'
  PRINT ''
  PRINT 'INSERT INTO #CustLink'
  PRINT 'EXEC dbo.FindOrderBookCustomerLinksCSV ' + @ACustomerNumberCSV
  PRINT ''
  PRINT 'CREATE TABLE #AutoUsers ([Number] [int] NOT NULL)'
  PRINT ''
  PRINT 'INSERT INTO #AutoUsers'
  PRINT '  SELECT UserIdNumber'
  PRINT '  FROM Personel WITH (NOLOCK)'
  PRINT '  WHERE UserTypeNumber IN (3, 4)'
  PRINT ''

  IF @SelectCountryNumbers <> ''
  BEGIN
    PRINT 'INSERT INTO #SelectCountryNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectCountryNumbers + ''')'
  END
  IF @SelectSegmentNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectSegmentNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectSegmentNumbers + ''')'
  END
  IF @SelectPointOfSaleNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectPointOfSaleNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectPointOfSaleNumbers + ''')'
  END
  IF @SelectRoomTypeNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectRoomTypeNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectRoomTypeNumbers + ''')'
  END
  IF @SelectHallTypeNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectHallTypeNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectHallTypeNumbers + ''')'
  END
  IF @MISValueTypes <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #MISValueTypes'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @MISValueTypes + ''')'
  END
  IF @SelectHotelNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectHotelNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectHotelNumbers + ''')'
    PRINT ''
    PRINT 'INSERT INTO #SelectHotelCustomerNumbers'
    PRINT 'SELECT CustomerNumber'
    PRINT 'FROM customer c WITH (NOLOCK)'
    PRINT 'INNER JOIN #SelectHotelNumbers sh ON c.HotelNumber = sh.No'
  END
  IF @SelectStatusNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectStatusNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectStatusNumbers + ''')'
  END
  IF @SelectStaffIDs <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectStaffIDs'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectStaffIDs + ''')'
  END
  IF @SelectPurposeCodeIds <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectPurposeCodeIds'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectPurposeCodeIds + ''')'
  END
  IF @SelectCampaignCodeNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectCampaignCodeNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectCampaignCodeNumbers + ''')'
  END
  IF @SelectGuestTypeNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectGuestTypeNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectGuestTypeNumbers + ''')'
  END
  IF @SelectProjectCodeNumbers <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectProjectCodeNumbers'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectProjectCodeNumbers + ''')'
  END
  IF @SelectProducts <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectProducts'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectProducts + ''')'
  END
  IF @SelectWares <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectWares'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectWares + ''')'
  END
  IF @SelectWeekDays <> ''
  BEGIN
    PRINT ''
    PRINT 'INSERT INTO #SelectWeekDays'
    PRINT 'SELECT Value'
    PRINT 'FROM dbo.SplitListMax(''' + @SelectWeekDays + ''')'
  END
  PRINT ''
END

--*****************
--History, booked
--*****************
IF (@DisplayType <> @DisplayTypeRes) --Reservations
  AND (@DisplayType <> @DisplayTypeWareCount)
  AND @SystemDateHistFromSP <> '20000101'
BEGIN
  IF @TypeGroupingValue = @TypeGroupingPaxPerRoom
  BEGIN
    SET @SQLStatement3 = @LF + 
      'UNION ALL /*Union6*/' + @LF +  @LF +
    
      'SELECT 0 AS BookedReservations, 0 AS ActualReservations, ' + @LF + 
      '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --20 felter
      --Extra last year
      '0, 0, 0, 0, 0, 0, ' + @LF + --6 felter 
      '0, 0, 0, 0, 0, 0, ' + @LF + 
      '0, ' + --ReservationStatus,
      '0, ' + --StaffID,
      '0, ' + --CampaignCode,
      '0, ' + --ProjectCode,
      '0, ' + --CountryCode
      '0, 0, 0, 0, ' + @LF + 
      '(BookedHistAdult + BookedHistChildren1 + BookedHistChildren2) / BookedHistNumberOfRooms AS PaxPerRoom, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --ActualHist, 12 fields
      'SUM(BookedHistAdult) AS BookedHistAdult, SUM(BookedHistChildren1) AS BookedHistChildren1, SUM(BookedHistChildren2) AS BookedHistChildren2, SUM(BookedHistNumberOfRooms) AS BookedHistNumberOfRooms,' + @LF +
      '0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + 
      '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist ' + @LF + 
      'FROM #tmp4' + @LF +
      'GROUP BY (BookedHistAdult + BookedHistChildren1 + BookedHistChildren2) / BookedHistNumberOfRooms'
  END
  ELSE
  BEGIN
    SET @SQLStatement3 = @LF +     
      'UNION ALL /*Union7*/' + @LF + @LF +
      
      'SELECT 0 AS BookedReservations, 0 AS ActualReservations, ' + @LF + 
      '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --20 felter
      --Extra last year
      '0, 0, 0, 0, 0, 0, ' + @LF + --6 felter
      @SQLDateStampHist + @LF +
      @SQLSegmentHistBook + @LF +
      @SQLPOSHistBook + @LF +
      @SQLRoomType + @LF +
      @SQLValueType + @LF +
      @SQLHotelNumbersHistBook + @LF +
      @SQLStatus + @LF +
      @SQLStaffHistBook + @LF +
      @SQLCampaignHistBook + @LF +
      @SQLProjectHistBook + @LF +
      @SQLCountryHistBook + @LF +
      @SQLGuestTypeHistBook + @SQLPurposeHistBook + @SQLProductsHistBook + @SQLWaresHistBook + '0, ' + @LF + --PaxPerRoom
      '0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + @LF + --ActualHist, 12 fields
      'SUM(Adult) AS BookedHistAdult, SUM(Children1) AS BookedHistChildren1, SUM(Children2) AS BookedHistChildren2, SUM(NumberOfRooms) AS BookedHistNumberOfRooms, SUM(Price) AS BookedHistPrice, ' + @LF +
      'SUM(VATOfPrice) AS BookedHistVATOfPrice, SUM(Package) AS BookedHistPackage, SUM(VATOfPackage) AS BookedHistVATOfPackage, ' + @LF +
      '0, 0, 0, ' + @LF + --RoomCount fields
      @SQLRoomCountInHistPeriodBook + @LF +
      '''' + CONVERT(VARCHAR, @SystemDateHistFromSP) + ''' AS SystemDateHist ' + @LF +
      'FROM MISHistory WITH (NOLOCK) ' + @LF
    IF ISNULL(@ACustomerNumberCSV, '') <> ''
    BEGIN
      SET @SQLStatement3 = @SQLStatement3 +
        'LEFT OUTER JOIN ReservationAddressXTab rax WITH (NOLOCK) ON rax.ReferenceNumber = MISHistory.FK_ReferenceNumber ' + @LF +
        '  AND rax.GuestNumber = MISHistory.FK_GuestNumber ' + @LF +
        'LEFT OUTER JOIN ReservationAddress ra WITH (NOLOCK) ON ra.AddressNumber = rax.AddressNumber ' + @LF +
        'LEFT OUTER JOIN Folios WITH (NOLOCK) ON Folios.FolioNumber = MISHistory.FK_FolioNumber ' + @LF + 
        'LEFT OUTER JOIN ReservationAddress raf WITH (NOLOCK) ON raf.AddressNumber = Folios.ReservationAddressNumber ' + @LF
    END
    SET @SQLStatement3 = @SQLStatement3 +
      'WHERE SystemDate = ''' + CONVERT(VARCHAR, @SystemDateHist, 23) + ''' ' + @LF + 
      'AND ' + @DateLimitField + ' <= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @EndDate), 23) + ''' ' + @LF + 
      'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, DATEADD(yy, - @HistYears, @StartDate), 23) + ''' ' + @LF + 
      'AND ' + @DateLimitField + ' >= ''' + CONVERT(VARCHAR, @SystemDateHist, 23) + ''' ' + @LF + 
      'AND (''' + @SelectSegmentNumbers + ''' = '''' OR FK_SegmentNumber IN (SELECT No FROM #SelectSegmentNumbers)) ' + @LF + 
      'AND (''' + @SelectPointOfSaleNumbers + ''' = '''' OR FK_PointOfSaleNumber IN (SELECT No FROM #SelectPointOfSaleNumbers)) ' + @LF +
      --Room- and hallType:
      'AND (''' + @SelectRoomTypeNumbers + ''' = '''' OR (RoomTypeNumber IN (SELECT No FROM #SelectRoomTypeNumbers) AND ValueType = 1)) ' + @LF + --mvtRooms
      'AND (''' + @SelectHallTypeNumbers + ''' = '''' OR (RoomTypeNumber IN (SELECT No FROM #SelectHallTypeNumbers) AND ValueType IN (2,3))) ' + @LF + --mvtHalls, mvtDaymeetings
      'AND (''' + @MISValueTypes + ''' = '''' OR ValueType IN (SELECT No FROM #MISValueTypes)) ' + @LF + 
      'AND (''' + @SelectHotelNumbers + ''' = '''' OR FK_HotelCustomerNumber IN (SELECT No FROM #SelectHotelCustomerNumbers)) ' + @LF + 
      'AND (''' + @SelectPurposeCodeIds + ''' = '''' OR FK_PurposeCodeNumber IN (SELECT No FROM #SelectPurposeCodeIds)) ' + @LF + 
      'AND (''' + @SelectGuestTypeNumbers + ''' = '''' OR FK_GuestTypeNumber IN (SELECT No FROM #SelectGuestTypeNumbers)) ' + @LF + 
      'AND (''' + @SelectProducts + ''' = '''' OR FK_ProdutNumber IN (SELECT No FROM #SelectProducts)) ' + @LF + 
      'AND (''' + @SelectWares + ''' = '''' OR FK_WareNumber IN (SELECT No FROM #SelectWares)) ' + @LF + 
      'AND (''' + @SelectWeekDays + ''' = '''' OR DATEPART(WEEKDAY, DateStamp) IN (SELECT No FROM #SelectWeekDays)) ' + @LF + 
      'AND ''' + @SelectCountryNumbers + ''' = '''' ' + @LF +      
--      'AND (''' + @SelectCountryNumbers + ''' = '''' OR CASE WHEN ra.CountryNumber IS NULL THEN raf.CountryNumber ELSE ra.CountryNumber END IN (SELECT No FROM #SelectCountryNumbers)) ' + @LF + 
      'AND (''' + @SelectStatusNumbers + ''' = '''' OR ReservationStatus IN (SELECT No FROM #SelectStatusNumbers)) ' + @LF + 
      'AND (''' + @SelectCampaignCodeNumbers + ''' = '''' OR FK_CampaignCode IN (SELECT No FROM #SelectCampaignCodeNumbers)) ' + @LF + 
      'AND (''' + @SelectProjectCodeNumbers + ''' = '''' OR FK_ProjectCode IN (SELECT No FROM #SelectProjectCodeNumbers)) ' + @LF
    IF ISNULL(@ACustomerNumberCSV, '') <> ''
    BEGIN
      SET @SQLStatement3 = @SQLStatement3 +
        'AND (ra.Link IN (SELECT CustomerNumber FROM #CustLink) OR ra.ContactPersonCustomerNumber IN (SELECT CustomerNumber FROM #CustLink) OR (MISHistory.FK_StaffId NOT IN (SELECT Number FROM #AutoUsers) AND raf.Link IN (SELECT CustomerNumber FROM #CustLink))) ' + @LF
    END
    SET @SQLStatement3 = @SQLStatement3 +
      'GROUP BY ' + @SQLTypeGroupingHistBook + ', ValueType '
  END

--  PRINT 'WITH @SQLStatement3'
  IF @IncludeExtraSale = 1
  BEGIN
    IF @ShowPrint = 1
    BEGIN
      --PRINT 'Extra (manual) sale last year'
      --PRINT '@IncludeExtraSale = 1' 
      PRINT @SQLStatementPaxPerRoomTmpTable
      PRINT @SQLStatement1 
      PRINT @SQLStatement1PiecesPaxActual
      PRINT @SQLStatement1PiecesPax
      PRINT @SQLStatement2
      PRINT @SQLStatement2a
      PRINT @SQLStatement3
      PRINT ' UNION ALL /*Union8*/' 
      PRINT @SQLStatement4
    END

    EXEC (@SQLStatementPaxPerRoomTmpTable + @SQLStatement1 + @SQLStatement1PiecesPaxActual + @SQLStatement1PiecesPax + @SQLStatement2 + @SQLStatement2a + @SQLStatement3 + ' UNION ALL /*Union9*/' + @SQLStatement4)
  END
  ELSE
  BEGIN
    IF @ShowPrint = 1
    BEGIN
    --PRINT 'Extra (manual) sale last year'
    --PRINT '@IncludeExtraSale = 0' 
      PRINT @SQLStatementPaxPerRoomTmpTable
      PRINT @SQLStatement1
      PRINT @SQLStatement1PiecesPaxActual
      PRINT @SQLStatement1PiecesPax
      PRINT @SQLStatement2
      PRINT @SQLStatement2a
      PRINT @SQLStatement3
    END

    EXEC (@SQLStatementPaxPerRoomTmpTable + @SQLStatement1 + @SQLStatement1PiecesPaxActual + @SQLStatement1PiecesPax + @SQLStatement2 + @SQLStatement2a + @SQLStatement3)
  END
END
ELSE
BEGIN
  --PRINT 'WITHOUT @SQLStatement3'
  IF @IncludeExtraSale = 1
  BEGIN
    IF @ShowPrint = 1
    BEGIN
      --PRINT 'Extra (manual) sale last year'
      --PRINT '@IncludeExtraSale = 1' 
      PRINT @SQLStatementPaxPerRoomTmpTable
      PRINT @SQLStatement1
      PRINT @SQLStatement1PiecesPaxActual
      PRINT @SQLStatement1PiecesPax
      PRINT @SQLStatement2
      PRINT @SQLStatement2a
      PRINT ' UNION ALL /*Union10*/'
      PRINT @SQLStatement4
    END

    EXEC (@SQLStatementPaxPerRoomTmpTable + @SQLStatement1 + @SQLStatement1PiecesPaxActual + @SQLStatement1PiecesPax + @SQLStatement2 + @SQLStatement2a + ' UNION ALL /*Union11*/' + @SQLStatement4)
  END
  ELSE
  BEGIN
    IF @ShowPrint = 1
    BEGIN
      --PRINT 'Extra (manual) sale last year'
      --PRINT '@IncludeExtraSale = 0'
      PRINT @SQLStatementPaxPerRoomTmpTable
      PRINT @SQLStatement1
      PRINT @SQLStatement1PiecesPaxActual
      PRINT @SQLStatement1PiecesPax
      PRINT @SQLStatement2
      PRINT @SQLStatement2a
    END
    EXEC (@SQLStatementPaxPerRoomTmpTable + @SQLStatement1 + @SQLStatement1PiecesPaxActual + @SQLStatement1PiecesPax + @SQLStatement2 + @SQLStatement2a)
  END
END

IF @ShowPrint = 1
BEGIN
  PRINT ''
  PRINT 'DROP TABLE #SelectCountryNumbers'
  PRINT 'DROP TABLE #SelectSegmentNumbers'
  PRINT 'DROP TABLE #SelectPointOfSaleNumbers'
  PRINT 'DROP TABLE #SelectRoomTypeNumbers'
  PRINT 'DROP TABLE #SelectHallTypeNumbers'
  PRINT 'DROP TABLE #MISValueTypes'
  PRINT 'DROP TABLE #SelectHotelNumbers'
  PRINT 'DROP TABLE #SelectHotelCustomerNumbers'
  PRINT 'DROP TABLE #SelectStatusNumbers'
  PRINT 'DROP TABLE #SelectStaffIDs'
  PRINT 'DROP TABLE #SelectPurposeCodeIds'
  PRINT 'DROP TABLE #SelectCampaignCodeNumbers'
  PRINT 'DROP TABLE #SelectGuestTypeNumbers'
  PRINT 'DROP TABLE #SelectProjectCodeNumbers'
  PRINT 'DROP TABLE #SelectProducts'
  PRINT 'DROP TABLE #SelectWares'
  PRINT 'DROP TABLE #SelectWeekDays'
  PRINT 'DROP TABLE #Refs'
  PRINT 'DROP TABLE #CustLink'
  PRINT 'DROP TABLE #AutoUsers'
  PRINT '----------------------------                    manuel run end here                 ---------------------------------'
  PRINT ''
  PRINT ''
END

IF OBJECT_ID('#Refs', 'U') IS NOT NULL
  DROP TABLE #Refs;

DROP TABLE #SelectCountryNumbers
DROP TABLE #SelectSegmentNumbers
DROP TABLE #SelectPointOfSaleNumbers
DROP TABLE #SelectRoomTypeNumbers
DROP TABLE #SelectHallTypeNumbers
DROP TABLE #MISValueTypes
DROP TABLE #SelectHotelNumbers
DROP TABLE #SelectHotelCustomerNumbers
DROP TABLE #SelectStatusNumbers
DROP TABLE #SelectStaffIDs
DROP TABLE #SelectPurposeCodeIds
DROP TABLE #SelectCampaignCodeNumbers
DROP TABLE #SelectGuestTypeNumbers
DROP TABLE #SelectProjectCodeNumbers
DROP TABLE #SelectProducts
DROP TABLE #SelectWares
DROP TABLE #SelectWeekDays
DROP TABLE #AutoUsers
DROP TABLE #CustLink