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(100) = '', --comma separated list 
                          @SelectSegmentNumbers varchar(50) = '',
                          @SelectPointOfSaleNumbers varchar(50) = '',
                          @SelectRoomTypeNumbers varchar(50) = '',
                          @SelectHallTypeNumbers varchar(50) = '',
                          @MISValueTypes varchar(20) = '1,2,3,4,5', 
                          @SelectHotelNumbers varchar(50) = '',
                          @SelectStatusNumbers varchar(20) = '',
                          @SelectStaffIDs varchar(100) = '',
                          @SelectPurposeCodeIds varchar(30) = '',
                          @SelectCampaignCodeNumbers varchar(30) = '',
                          @SelectGuestTypeNumbers varchar(30) = '',
                          @SelectProjectCodeNumbers varchar(20) = '',
                          @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
						  
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

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 @LF char(2);
Declare @SQLStatement1 varchar(max);
Declare @SQLStatement1PiecesPax varchar(max) = ''
DECLARE @SQLRefIn VARCHAR(MAX)
Declare @SQLStatement2 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    --local: 6
Declare @TypeGroupingPOS int = 6        --local: 7
Declare @TypeGroupingRoomType int = 7   --local: 8
Declare @TypeGroupingHallType int = 8   --local: 9
Declare @TypeGroupingStatus int = 9     --local: 10 
Declare @TypeGroupingStaff int = 10     --local: 11 
Declare @TypeGroupingPurpose int = 11   --local: 12
Declare @TypeGroupingCampaign int = 12  --local: 13 
Declare @TypeGroupingGuestType int = 13 --local: 14
Declare @TypeGroupingProject int = 14   --local: 15 
Declare @TypeGroupingCountry int = 15   --local: 16 
Declare @TypeGroupingHotel int = 16     --local: 17
Declare @TypeGroupingProduct int = 17   --local: 18
Declare @TypeGroupingWare int = 19      --local: 20
Declare @TypeGroupingPaxPerRoom int = 20--local: 21

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 @SQLHallType varchar(50); --??
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);

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)

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)

Declare @SystemDate 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)

IF @StartDate <= @SystemDate AND @EndDate >= @SYSTEMDATE
Begin
  Set @SystemDateHist = DateAdd(yyyy, -1, @SystemDate);
End ELSE
Begin
  Set @SystemDateHist = @SystemDate;
  SELECT @SystemDateHist = DATEADD(year, datepart(year, @StartDate) - datepart(year, @SystemDateHist) - 1, @SystemDateHist)
End
--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, -1, @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
BEGIN
  Set @SystemDateHistFromSP = @SystemDateHist
END

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 = 3 or UserTypeNumber = 4

Set @LF = CHAR(13)+CHAR(10);

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 DATEFIRST 1; --mandag

If @TypeGroupingValue = @TypeGroupingDay
Begin
  If @DisplayType = @DisplayTypeRes
  Begin
    Set @SQLTypeGrouping = 'r.DepartureDate '
    Set @SQLTypeGroupingHist = 'DateAdd(yy, 1, r.DepartureDate) '
    Set @SQLTypeGroupingHistBook = 'DateAdd(yy, 1, r.DepartureDate) '
    Set @SQLDateStamp = 'r.DepartureDate as DateStamp, '
    Set @SQLDateStampHist = 'DateAdd(yyyy, 1, r.DepartureDate) as DateStamp, '
  End Else
  Begin
    Set @SQLTypeGrouping = 'DateStamp '
    Set @SQLTypeGroupingHist = 'DateStamp ' --'DateAdd(yy, 1, DateStamp) '
    Set @SQLTypeGroupingHistBook = 'DateStamp ' --'DateAdd(yy, 1, DateStamp) '
    Set @SQLDateStamp = 'DateStamp, '
    Set @SQLDateStampHist = 'DateAdd(yyyy, 1, DateStamp) as DateStamp, '
	--If @DisplayType = @DisplayTypeToPerPieces and @MISValueTypes = ',1'
	If @SpecialMode = 2 --RevPar
	Begin
	  Set @SQLRoomCountInPeriod = 
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), @StartDate, 112) +
	  ''', ''' + Convert(CHAR(8), @SystemDate, 112) + 
	  ''', ''' + @SelectHotelNumbers +
	  ''', 1, DateStamp, ''ACTUAL'') as RoomCountActualInPeriod, ' +
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), @SystemDate, 112) +
      ''', ''' + Convert(CHAR(8), @EndDate, 112) + 
	  ''', ''' + @SelectHotelNumbers +
	  ''', 1, DateStamp, ''BOOK'') as RoomCountBookedInPeriod, '
	  
	  Set @SQLRoomCountInHistPeriodActual = 
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), DateAdd(yyyy, -1, @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(yyyy, -1, @EndDate), 112) + 
      ''', ''' + @SelectHotelNumbers +
      ''', 1, DateStamp, ''BOOK'') as RoomCountBookedInHistPeriod, '
	End
  End
End
If @TypeGroupingValue = @TypeGroupingWeek
Begin 
  If @DisplayType = @DisplayTypeRes
  Begin
    Set @SQLTypeGrouping = 'DatePart(yy, r.DepartureDate), DatePart(ISO_WEEK, r.DepartureDate) '
    Set @SQLTypeGroupingHist = 'DatePart(yy, DateAdd(yyyy, 1, r.DepartureDate)), DatePart(ISO_WEEK, DateAdd(yyyy, 1, r.DepartureDate)) '
    Set @SQLTypeGroupingHistBook = 'DatePart(yy, DateAdd(yy, 1, DateStamp)), DatePart(ISO_WEEK, DateAdd(yyyy, 1, DateStamp)) '
    Set @SQLDateStamp = 'MIN(r.DepartureDate) as DateStamp, '
	Set @SQLDateStampHist = 'MIN(DateAdd(yyyy, 1, r.DepartureDate)) as DateStamp, '
  End Else
  Begin
    Set @SQLTypeGrouping = 'DatePart(yy, DateStamp), DatePart(ISO_WEEK, DateStamp) '
    Set @SQLTypeGroupingHist = 'DatePart(yy, DateAdd(yyyy, 1, DateStamp)), DatePart(ISO_WEEK, DateAdd(yyyy, 1, DateStamp)) '
    Set @SQLTypeGroupingHistBook = 'DatePart(yy, DateAdd(yy, 1, DateStamp)), DatePart(ISO_WEEK, DateAdd(yyyy, 1, DateStamp)) '
    Set @SQLDateStamp = 'MIN(DateStamp) as DateStamp, '
    Set @SQLDateStampHist = 'MIN(DateAdd(yyyy, 1, DateStamp)) as DateStamp, '
	If @SpecialMode = 2 --RevPar
	Begin
	  Set @SQLRoomCountInPeriod = 
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), @StartDate, 112) +
	  ''', ''' + Convert(CHAR(8),  @SystemDate, 112) + 
	  ''', ''' + @SelectHotelNumbers +
      ''', 2, MIN(DateStamp), ''ACTUAL'') as RoomCountActualInPeriod, ' +
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8),  @SystemDate, 112) +
	  ''', ''' + Convert(CHAR(8), @EndDate, 112) + 
	  ''', ''' + @SelectHotelNumbers +
      ''', 2, MIN(DateStamp), ''BOOK'') as RoomCountBookedInPeriod, '
	  
	  Set @SQLRoomCountInHistPeriodActual = 
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), DateAdd(yyyy, -1, @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(yyyy, -1, @EndDate), 112) + 
      ''', ''' + @SelectHotelNumbers +
      ''', 2, MIN(DateStamp), ''BOOK'') as RoomCountBookedInHistPeriod, '
	End
  End
End
If @TypeGroupingValue = @TypeGroupingMonth
Begin 
  If @DisplayType = @DisplayTypeRes
  Begin
    Set @SQLTypeGrouping = 'DatePart(yy, r.DepartureDate), DatePart(mm, r.DepartureDate) '
    Set @SQLTypeGroupingHist = 'DatePart(yy, DateAdd(yyyy, 1, r.DepartureDate)), DatePart(mm, DateAdd(yyyy, 1, r.DepartureDate)) '
    Set @SQLTypeGroupingHistBook = 'DatePart(yy, DateAdd(yy, 1, r.DepartureDate)), DatePart(mm, DateAdd(yy, 1, r.DepartureDate)) '
    Set @SQLDateStamp = 'MIN(r.DepartureDate) as DateStamp, '
    Set @SQLDateStampHist = 'MIN(DateAdd(yyyy, 1, r.DepartureDate)) as DateStamp, '
  End Else
  Begin
    Set @SQLTypeGrouping = 'DatePart(yy, DateStamp), DatePart(mm, DateStamp) '
    Set @SQLTypeGroupingHist = 'DatePart(yy, DateAdd(yyyy, 1, DateStamp)), DatePart(mm, DateAdd(yyyy, 1, DateStamp)) '
    Set @SQLTypeGroupingHistBook = 'DatePart(yy, DateAdd(yy, 1, DateStamp)), DatePart(mm, DateAdd(yy, 1, DateStamp)) '
    Set @SQLDateStamp = 'MIN(DateStamp) as DateStamp, '
    Set @SQLDateStampHist = 'MIN(DateAdd(yyyy, 1, DateStamp)) as DateStamp, '
	If @SpecialMode = 2 --RevPar
	Begin
	  Set @SQLRoomCountInPeriod = 
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), @StartDate, 112) +
      ''', ''' + Convert(CHAR(8), @SystemDate, 112) + 
      ''', ''' + @SelectHotelNumbers +
      ''', 3, MIN(DateStamp), ''ACTUAL'') as RoomCountActualInPeriod, ' + @LF +
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), @SystemDate, 112) +
      ''', ''' + Convert(CHAR(8), @EndDate, 112) + 
      ''', ''' + @SelectHotelNumbers +
      ''', 3, MIN(DateStamp), ''BOOK'') as RoomCountBookedInPeriod, '

	  Set @SQLRoomCountInHistPeriodActual = 
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), DateAdd(yyyy, -1, @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(yyyy, -1, @EndDate), 112) + 
      ''', ''' + @SelectHotelNumbers +
      ''', 3, MIN(DateStamp), ''BOOK'') as RoomCountBookedInHistPeriod, ' + @LF

	End
  End
End
If @TypeGroupingValue = @TypeGroupingYear
Begin
  If @DisplayType = @DisplayTypeRes
  Begin
    Set @SQLTypeGrouping = 'DatePart(yy, r.DepartureDate) '
    Set @SQLTypeGroupingHist =     'DatePart(yy, DateAdd(yyyy, 1, r.DepartureDate)) '
    Set @SQLTypeGroupingHistBook = 'DatePart(yy, DateAdd(yy, 1, r.DepartureDate)) '
    Set @SQLDateStamp = 'MIN(r.DepartureDate) as DateStamp, '
    Set @SQLDateStampHist = 'MIN(DateAdd(yyyy, 1, r.DepartureDate)) as DateStamp, '
  End Else
  Begin
    Set @SQLTypeGrouping = 'DatePart(yy, DateStamp) '
    Set @SQLTypeGroupingHist =     'DatePart(yy, DateAdd(yyyy, 1, DateStamp)) '
    Set @SQLTypeGroupingHistBook = 'DatePart(yy, DateAdd(yy, 1, DateStamp)) '
    Set @SQLDateStamp = 'MIN(DateStamp) as DateStamp, '
    Set @SQLDateStampHist = 'MIN(DateAdd(yyyy, 1, DateStamp)) as DateStamp, '
	If @SpecialMode = 2 --RevPar
	Begin
	  Set @SQLRoomCountInPeriod = 
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), @StartDate, 112) +
      ''', ''' + Convert(CHAR(8), @SystemDate, 112) + 
      ''', ''' + @SelectHotelNumbers +
      ''', 4, MIN(DateStamp), ''ACTUAL'') as RoomCountActualInPeriod, ' +
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), @SYstemDate, 112) +
      ''', ''' + Convert(CHAR(8), @EndDate, 112) + 
      ''', ''' + @SelectHotelNumbers +
      ''', 4, MIN(DateStamp), ''BOOK'') as RoomCountBookedInPeriod, '
	  
	  Set @SQLRoomCountInHistPeriodActual = 
	  'dbo.GetMISRoomCountInPeriod(''' + Convert(CHAR(8), DateAdd(yyyy, -1, @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(yyyy, -1, @EndDate), 112) + 
      ''', ''' + @SelectHotelNumbers +
      ''', 4, MIN(DateStamp), ''BOOK'') as RoomCountBookedInHistPeriod, ' + @LF
	End
  End
End
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
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
If @TypeGroupingValue = @TypeGroupingRoomType
Begin
  Set @SQLTypeGrouping = 'RoomTypeNumber '
  Set @SQLTypeGroupingHist = 'RoomTypeNumber '
  Set @SQLTypeGroupingHistBook = 'RoomTypeNumber '
  Set @SQLRoomType = 'RoomTypeNumber, '
End
If @TypeGroupingValue = @TypeGroupingHallType
Begin
  Set @SQLTypeGrouping = 'RoomTypeNumber ' 
  Set @SQLTypeGroupingHist = 'RoomTypeNumber '
  Set @SQLTypeGroupingHistBook = 'RoomTypeNumber '
  Set @SQLRoomType = 'RoomTypeNumber, ' 
End
If @TypeGroupingValue = @TypeGroupingStatus
Begin
  Set @SQLTypeGroupingHistBook = 'RoomTypeNumber '
  Set @SQLTypeGrouping = 'ReservationStatus '
  Set @SQLTypeGroupingHist = 'ReservationStatus '
  Set @SQLStatus = 'ReservationStatus, ' 
End
If @TypeGroupingValue = @TypeGroupingStaff
Begin
  Set @SQLTypeGrouping = 'StaffID '
  Set @SQLTypeGroupingHist = 'StaffID '
  Set @SQLStaff = 'StaffID, ' 
End
If @TypeGroupingValue = @TypeGroupingPurpose
Begin
  Set @SQLTypeGrouping = 'PurposeCode ' 
  Set @SQLTypeGroupingHist = 'PurposeCode ' 
  Set @SQLTypeGroupingHistBook = 'FK_PurposeCodeNumber '
  Set @SQLPurpose = 'PurposeCode, ' 
  Set @SQLPurposeHistBook = 'FK_PurposeCodeNumber, '
End
If @TypeGroupingValue = @TypeGroupingCampaign
Begin
  Set @SQLTypeGrouping = 'CampaignCode ' 
  Set @SQLTypeGroupingHist = 'CampaignCode ' 
  Set @SQLCampaign = 'CampaignCode, ' 
End
If @TypeGroupingValue = @TypeGroupingGuestType
Begin
  Set @SQLTypeGrouping = 'GuestType ' 
  Set @SQLTypeGroupingHist = 'GuestType '
  Set @SQLTypeGroupingHistBook = 'FK_GuestTypeNumber '
  Set @SQLGuestType = 'GuestType, ' 
  Set @SQLGuestTypeHistBook = 'FK_GuestTypeNumber, '
End
If @TypeGroupingValue = @TypeGroupingProject
Begin
  Set @SQLTypeGrouping = 'ProjectCode '
  Set @SQLTypeGroupingHist = 'ProjectCode ' 
  Set @SQLProject = 'ProjectCode, ' 
End
If @TypeGroupingValue = @TypeGroupingCountry
Begin 
  Set @SQLTypeGrouping = 'ReservationAddress.CountryNumber, raf.CountryNumber, ReservationAddress.CountryCode '
  Set @SQLTypeGroupingHist = 'ReservationAddress.CountryNumber, raf.CountryNumber, ReservationAddress.CountryCode '
  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, ';
End
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
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

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

--@TypeGroupingValue = @TypeGroupingPaxPerRoom: En helt anden @SQLStatement1, 2, 3

Print '@DisplayType=' + convert(varchar, @DisplayType) + ', @TypeGroupingValue=' + convert(varchar, @TypeGroupingValue) + ', @SQLTypeGrouping=' + @SQLTypeGrouping + ', @SQLTypeGroupingHist=' + @SQLTypeGroupingHist

Set @MainCourseJoin = '';
Set @MainCourseWhere = '';
If @DisplayType = @DisplayTypeRes --Reservation
Begin
  Set @SQLDisplayTypeGrouping = ', r.DepartureDate '
  Set @DateLimitField = 'r.Departuredate'; 
  Set @SQLValueType = '0 as ValueType, '; 
  If @DisplayResSubType = 0 --Only normal res.
  Begin
      Set @MainCourseJoin = 'LEFT OUTER JOIN ReservationReservationXtab x on r.ReferenceNumber = x.FK_ReferenceNumber ' + @LF 
	  Set @MainCourseWhere = 'and x.FK_ReferenceNumber is null ' + @LF 
  End 
  If @DisplayResSubType = 1   --Only main course res.
  Begin
      Set @MainCourseJoin = 'INNER JOIN ReservationReservationXtab x on r.ReferenceNumber = x.FK_ReferenceNumber ' + @LF 
  End
  If @DisplayResSubType = 2   --All res.
  Begin
      Set @MainCourseJoin = '' 
  End
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 ' +
'   AND ReservationAddressXTab.GuestNumber = MIS.GuestNumber ' +
'   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

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


Set @PaxPerResWhere = ''
If IsNull(@MinPaxPerRes, 0) <> 0 or IsNull(@MaxPaxPerRes, 0) <> 0
Begin
  If IsNull(@MaxPaxPerRes, 0) = 0
  Begin
    Set @MaxPaxPerRes = 100000 --stor!
  End
  Set @PaxPerResWhere = @LF + '/*Min/max pax*/' + @LF +
  'AND MIS.ReferenceNumber in ' + @LF + 
  '  (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 ' + @LF +
'    (''' + @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


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
  Begin
    Set @SQLActualPaxPieces = @SQLActualPaxPieces + 'SUM(ActualWareCount) as ActualNumberOfRooms, --ActualWareCount' + @LF
  End Else
  Begin
    Set @SQLActualPaxPieces = @SQLActualPaxPieces + 'SUM(ActualNumberOfRooms) as ActualNumberOfRooms, '
  End   
  --Hist
  Set @SQLActualHistPaxPieces = @LF +
'SUM(MIS.ActualAdult) as ActualHistAdult, ' +
'SUM(MIS.ActualChildren1) as ActualHistChildren1, ' +
  'SUM(MIS.ActualChildren2) as ActualHistChildren2, '
  If @DisplayType = @DisplayTypeWareCount
  Begin
    Set @SQLActualHistPaxPieces = @SQLActualHistPaxPieces + 'SUM(ActualWareCount) as ActualHistNumberOfRooms, --ActualWareCount' + @LF
  End Else
  Begin
    Set @SQLActualHistPaxPieces = @SQLActualHistPaxPieces + 'SUM(MIS.ActualNumberOfRooms) as ActualHistNumberOfRooms, ' 
  End
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 @SQLStatement1 = --booked
	'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, ' +
    '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 +
	@SQLPOS +
	@SQLRoomType +
	'0 as ValueType, ' +  
	@SQLHotelNumbers +
	@SQLStatus +
	@SQLStaff +
	@SQLCampaign +
	@SQLProject + 
	@SQLCountry +
	@SQLGuestType +
	@SQLPurpose +
	@SQLProducts + @LF +
	@SQLWares + @LF +
    '(BookedAdult+BookedChildren1+BookedChildren2) / IsNull(NULLIF(BookedNumberOfRooms, 0), 1) 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, ' +
	'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, ' + @LF +
	'0 as RoomCountBookedInHistPeriod, ' + @LF +
	'''' + Convert(varchar, @SystemDateHistFromSP) + ''' as SystemDateHist ' + @LF +
    @FromJoin + @LF +
    'WHERE ValueType = 1 ' + --Rooms
    'AND BookedAdult + BookedChildren1 + BookedChildren2 > 0 ' +
	'AND ' + @DateLimitField + ' <= ''' + Convert(varchar, @EndDate, 112) + ''' AND ' + @DateLimitField + ' >= ''' + Convert(VarChar,@StartDate, 112) + ''' ' + @LF +
	@StaffCondition +
	@WhereStandard + 
	'Group by (BookedAdult+BookedChildren1+BookedChildren2) / IsNull(NULLIF(BookedNumberOfRooms, 0), 1)' + @LF +

    'UNION ALL ' + @LF +
    --Actual
    'SELECT 0,0, 0,0,0,0,0,0, ' + @LF +
    'SUM(ActualAdult) as ActualAdult, ' +
    'SUM(ActualChildren1) as ActualChildren1, ' +
    'SUM(ActualChildren2) as ActualChildren2, ' +
    'SUM(ActualNumberOfRooms) as ActualNumberOfRooms, ' + @LF +
	'0,0,0,0,0,0, ' + @LF +
    '0,0,0,0, ' + @LF +
	'0,0,0,0,0,0, ' + @LF +
	@SQLDateStamp + @LF + 
	@SQLSegment +
	@SQLPOS +
	@SQLRoomType +
	'0 as ValueType, ' +  
	@SQLHotelNumbers +
	@SQLStatus +
	@SQLStaff +
	@SQLCampaign +
	@SQLProject + 
	@SQLCountry +
	@SQLGuestType +
	@SQLPurpose +
	@SQLProducts + @LF +
	@SQLWares + @LF +
	'(ActualAdult + ActualChildren1 + ActualChildren2) / IsNull(NULLIF(ActualNumberOfRooms, 0), 1) as PaxPerRoom, ' + @LF +
	--History part, 20 fields
	'0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,  ' + @LF +
	@SQLRoomCountInPeriod + @LF +
	'0 as RoomCountActualInHistPeriod, ' + @LF +
	'0 as RoomCountBookedInHistPeriod, ' + @LF +
	'''' + Convert(varchar, @SystemDateHistFromSP) + ''' as SystemDateHist ' + @LF +
    @FromJoin + @LF +
    'WHERE ValueType = 1 ' + --Rooms
    'AND ActualAdult+ActualChildren1+ActualChildren2 > 0 ' +
	'AND ' + @DateLimitField + ' <= ''' + Convert(varchar, @EndDate, 112) + ''' AND ' + @DateLimitField + ' >= ''' + Convert(VarChar,@StartDate, 112) + ''' ' + @LF +
	@StaffCondition +
	@WhereStandard + 
    'Group by (ActualAdult+ActualChildren1+ActualChildren2) / IsNull(NULLIF(ActualNumberOfRooms, 0), 1) ' + @LF
	
End Else
Begin
SET @SQLStatement1 =
'SELECT ' + @LF 
If @DisplayType = @DisplayTypeRes --Reservations
Begin
  SET @SQLStatement1 = @SQLStatement1 +
  'CASE WHEN r.Departuredate < ' + Convert(varchar, @SystemDate, 112) + ' THEN 0 ELSE count(distinct MIS.ReferenceNumber) END as BookedReservations, ' + @LF +
  'CASE WHEN r.Departuredate >= ' + Convert(varchar, @SystemDate, 112) + ' THEN 0 ELSE count(distinct MIS.ReferenceNumber) END as ActualReservations, ' + @LF 
End Else
Begin
  SET @SQLStatement1 = @SQLStatement1 +
  '0 as BookedReservations, ' + 
  '0 as ActualReservations, ' + @LF 
End
SET @SQLStatement1 = @SQLStatement1 + 
'SUM(ActualPriceInclPackage) as ActualPriceInclPackage, ' +
'SUM(ActualPriceInclPackageExVat) as ActualPriceInclPackageExVat, ' +
'SUM(ActualPriceExPackage) as ActualPriceExPackage, ' +
'SUM(ActualPriceExPackageExVat) as ActualPriceExPackageExVat, ' +
'SUM(ActualOnlyPackage) as ActualOnlyPackage, ' +
'SUM(ActualOnlyPackageExVat) as ActualOnlyPackageExVat, ' +
@SQLActualPaxPieces + @LF +
'SUM(BookedPriceInclPackage) as BookedPriceInclPackage, ' +
'SUM(BookedPriceInclPackageExVat) as BookedPriceInclPackageExVat, ' +
'SUM(BookedPriceExPackage) as BookedPriceExPackage, ' +
'SUM(BookedPriceExPackageExVat) as BookedPriceExPackageExVat, ' +
'SUM(BookedOnlyPackage) as BookedOnlyPackage, ' +
'SUM(BookedOnlyPackageExVat) as BookedOnlyPackageExVat, ' +
'SUM(BookedAdult) as BookedAdult, ' +
'SUM(BookedChildren1) as BookedChildren1, ' +
'SUM(BookedChildren2) as BookedChildren2, ' 
If @DisplayType = @DisplayTypeWareCount
Begin
  SET @SQLStatement1 = @SQLStatement1 + 'SUM(BookedWareCount) as BookedNumberOfRooms, --BookedWareCount' + @LF
End Else
Begin
  SET @SQLStatement1 = @SQLStatement1 + 'SUM(BookedNumberOfRooms) as BookedNumberOfRooms, ' + @LF
End
--Extra last year
SET @SQLStatement1 = @SQLStatement1 +
'0 as ExtraPriceInclPackage, ' +
'0 as ExtraPriceInclPackageExVat, ' +
'0 as ExtraPriceExPackage, ' +
'0 as ExtraPriceExPackageExVat, ' +
'0 as ExtraOnlyPackage, ' +
'0 as ExtraOnlyPackageExVat, ' +
@SQLDateStamp + @LF + 
@SQLSegment +
@SQLPOS +
@SQLRoomType +
@SQLValueType +  
@SQLHotelNumbers +
@SQLStatus +
@SQLStaff +
@SQLCampaign +
@SQLProject + 
@SQLCountry +
@SQLGuestType +
@SQLPurpose +
@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, ' +
'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, ' + @LF +
'0 as RoomCountBookedInHistPeriod, ' + @LF +
'''' + Convert(varchar, @SystemDateHistFromSP) + ''' as SystemDateHist ' + @LF +
	@FromJoin + @LF +
'WHERE 1=1 ' + @LF +
'and ' + @DateLimitField + ' <= ''' + Convert(varchar, @EndDate, 112) + ''' and ' + @DateLimitField + ' >= ''' + Convert(VarChar,@StartDate, 112) + ''' ' + @LF +
@StaffCondition +
@WhereStandard + 
'Group by  ' + @LF +
@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 +
    '  WHERE 1=1 ' + @LF +
    '  and ' + @DateLimitField + ' <= ''' + Convert(varchar, @EndDate, 112) + ''' 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 ' + @LF +	
  'SELECT 0, 0, ' + @LF +
  '0, 0, 0, 0, 0, 0, ' + @LF +
  'Sum(ActualAdult) as ActualAdult, Sum(ActualChildren1) as ActualChildren1, Sum(ActualChildren2) as ActualChildren2, Sum(ActualNumberOfRooms) as ActualNumberOfRooms, ' + @LF +
  '0,0,0,0,0,0,0,0,0,0, ' + @LF +  --Booked
  '0,0,0,0,0,0, ' + @LF + --Extra	
  @SQLDateStamp + @LF +
  @SQLSegment +
  @SQLPOS +
  @SQLRoomType +
  @SQLValueType +  
  @SQLHotelNumbers +
  @SQLStatus +
  @SQLStaff +
  @SQLCampaign +
  @SQLProject + 
  @SQLCountry +
  @SQLGuestType +
  @SQLPurpose +
  @SQLProducts + @LF +
  @SQLWares + @LF +
  '0, ' + @LF +
  '0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, ' + @LF + --20 fields (history)
  '0, 0, 0, 0, ' + @LF +  --RoomCount fields
  '''' + Convert(varchar, @SystemDateHistFromSP) + ''' as SystemDateHist ' + @LF +
  @FromJoin + 
  'WHERE 1=1 ' +
  'and ' + @DateLimitField + ' <= ''' + Convert(varchar, @EndDate, 112) + ''' and ' + @DateLimitField + ' >= ''' + Convert(VarChar,@StartDate, 112) + ''' ' + @LF +
  --Part of "@WhereStandard"  
  'and ' + @LF +
  '   (''' + @SelectRoomTypeNumbers + ''' = '''' or (MIS.RoomTypeNumber in (SELECT No FROM #SelectRoomTypeNumbers) AND ValueType = 1)) ' + @LF + --mvtRooms
  '   AND ' + @LF +
  '    (''' + @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 (''' + @SelectGuestTypeNumbers + ''' = '''' or r.GuestType IN (SELECT No FROM #SelectGuestTypeNumbers)) ' + @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 (' + @LF + --from transactions
  '  SELECT distinct MIS.ReferenceNumber ' + @LF +
  @FromJoin +
  '  WHERE 1=1 ' + @LF +
  '  and ' + @DateLimitField + ' <= ''' + Convert(varchar, @EndDate, 112) + ''' and ' + @DateLimitField + ' >= ''' + Convert(VarChar,@StartDate, 112) + ''' ' + @LF +
  @StaffCondition +
  @WhereStandard +
  ') ' + @LF + 
*/
  'AND MIS.ReferenceNumber IN (SELECT Ref FROM #Refs)' + @LF +
  'Group by ' + @LF +
  @SQLTypeGrouping + @SQLDisplayTypeGrouping --+ @SQLRefNumberGrouping
End

Print @LF + '@SQLStatement1 = ' + @SQLStatement1 + @LF + @LF
Print '@SQLStatement1PiecesPax = ' + @SQLStatement1PiecesPax + @LF

Set @SQLStatement1 = @SQLStatement1 + @SQLStatement1PiecesPax


--*****************
--History, actual
--*****************
If @TypeGroupingValue = @TypeGroupingPaxPerRoom
Begin
SET @SQLStatement2 = @LF + 'UNION ALL ' + @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)
	'(ActualAdult + ActualChildren1 + ActualChildren2) / IsNull(NULLIF(ActualNumberOfRooms, 0), 1) as PaxPerRoom, ' + @LF +
	'0, 0, ' +
    '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, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,' + @LF + --18 felter
	'''' + Convert(varchar, @SystemDateHistFromSP) + ''' as SystemDateHist ' + @LF +
	@FromJoin + @LF +
	'WHERE ActualAdult + ActualChildren1 + ActualChildren2 > 0 ' + @LF +
	'and ValueType = 1 ' + @LF +
	'and ' + @DateLimitField + ' <= ''' + Convert(VarChar, DateAdd(yyyy, -1, @EndDate), 112) + ''' and ' + @DateLimitField + ' >= ''' + Convert(VarChar, DateAdd(yyyy, -1, @StartDate), 112) + ''' ' + @LF +
	'and ' + @DateLimitField + ' < ''' + Convert(VarChar, @SystemDateHist, 112) + ''' ' + @LF +
	@StaffConditionHist +
	@WhereStandard +
    'Group by (ActualAdult + ActualChildren1 + ActualChildren2) / IsNull(NULLIF(ActualNumberOfRooms, 0), 1) ' 
End Else
Begin
	SET @SQLStatement2 = @LF + 'UNION ALL ' + @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 +
@SQLPOS +
@SQLRoomType +
@SQLValueType +  
@SQLHotelNumbers +
@SQLStatus +
@SQLStaff +
@SQLCampaign +
@SQLProject + 
@SQLCountry +
@SQLGuestType +
@SQLPurpose +
@SQLProducts + 
	@SQLWares +
	'0 as PaxPerRoom, '
--Actual history part
If @DisplayType = @DisplayTypeRes --Reservations
Begin
  SET @SQLStatement2 = @SQLStatement2 +
  'CASE WHEN r.Departuredate < ' + Convert(varchar, @SystemDateHist, 112) + ' THEN 0 ELSE count(distinct MIS.ReferenceNumber) END as BookedHistReservations, ' + @LF +
  'CASE WHEN r.Departuredate >= ' + Convert(varchar, @SystemDateHist, 112) + ' THEN 0 ELSE count(distinct MIS.ReferenceNumber) END as ActualHistReservations, ' + @LF 
End Else
Begin
  SET @SQLStatement2 = @SQLStatement2 +
  '0 as BookedHistReservations, ' + 
  '0 as ActualHistReservations, ' + @LF 
End
SET @SQLStatement2 = @SQLStatement2 +
@SQLActualHistPaxPieces +
'SUM(ActualPriceInclPackage) as ActualHistPriceInclPackage, ' + 
'SUM(ActualPriceInclPackageExVat) as ActualHistPriceInclPackageExVat, ' +
'SUM(ActualPriceExPackage) as ActualHistPriceExPackage, ' +
'SUM(ActualPriceExPackageExVat) as ActualHistPriceExPackageExVat, ' +
'SUM(ActualOnlyPackage) as ActualHistOnlyPackage, ' +
'SUM(ActualOnlyPackageExVat) as ActualHistOnlyPackageExVat, ' +
'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 +
'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 ' + 
'   AND ReservationAddressXTab.GuestNumber = MIS.GuestNumber ' + 
'   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 +
'WHERE 1=1 ' + @LF +
'and ' + @DateLimitField + ' <= ''' + Convert(VarChar, DateAdd(yyyy, -1, @EndDate), 112) + ''' and ' + @DateLimitField + ' >= ''' + Convert(VarChar, DateAdd(yyyy, -1, @StartDate), 112) + ''' ' + @LF +
'and ' + @DateLimitField + ' < ''' + Convert(VarChar, @SystemDateHist, 112) + ''' ' + @LF +
@StaffConditionHist +
@WhereStandard +
'Group by  ' + @LF +
@SQLTypeGroupingHist + @SQLDisplayTypeGrouping 
---------------------------------------------
--UNION with statement finding pieces and pax
If @DisplayType IN (@DisplayTypePieces, @DisplayTypePersons, @DisplayTypeToPerPieces, @DisplayTypeToPerPersons) or @TypeGroupingValue = @TypeGroupingStaff
Begin
  Set @SQLStatement2 = @SQLStatement2 + @LF + @LF +
  'UNION ALL ' + @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 +
  @SQLPOS +
  @SQLRoomType +
  @SQLValueType +  
  @SQLHotelNumbers +
  @SQLStatus +
  @SQLStaff +
  @SQLCampaign +
  @SQLProject + 
  @SQLCountry +
  @SQLGuestType +
  @SQLPurpose +
  @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 +
  'WHERE 1=1 ' +
  'and ' + @DateLimitField + ' <= ''' + Convert(VarChar, DateAdd(yyyy, -1, @EndDate), 112) + ''' and ' + @DateLimitField + ' >= ''' + Convert(VarChar, DateAdd(yyyy, -1, @StartDate), 112) + ''' ' + @LF +
  'and ' + @DateLimitField + ' < ''' + Convert(VarChar, @SystemDateHist, 112) + ''' ' + @LF +
  'and MIS.ReferenceNumber in (' + @LF + --from transactions
  '  SELECT distinct MIS.ReferenceNumber ' + @LF +
  @FromJoin +
  'WHERE 1=1 ' + @LF +
  'and ' + @DateLimitField + ' <= ''' + Convert(VarChar, DateAdd(yyyy, -1, @EndDate), 112) + ''' and ' + @DateLimitField + ' >= ''' + Convert(VarChar, DateAdd(yyyy, -1, @StartDate), 112) + ''' ' + @LF +
  'and ' + @DateLimitField + ' < ''' + Convert(VarChar, @SystemDateHist, 112) + ''' ' + @LF +
  @StaffConditionHist + 
  @WhereStandard +
  ') ' + @LF + 
  'Group by ' + @LF +
  @SQLTypeGrouping + @SQLDisplayTypeGrouping --+ @SQLRefNumberGrouping
End
End
 
Print 'Actual history'
Print '@SQLStatement2 = ' + @SQLStatement2 + @LF + @LF


If @IncludeExtraSale = 1
Begin
  --*****************
  --Extra last year
  --*****************
  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, ' +
  'SUM(ActualOnlyPackage) as ExtraOnlyPackage, ' +
  'SUM(ActualOnlyPackageExVat) as ExtraOnlyPackageExVat, ' +
  @SQLDateStampHist + @LF +
  @SQLSegment +
  @SQLPOS +
  @SQLRoomType +
  @SQLValueType +  
  @SQLHotelNumbers +
  @SQLStatus +
  @SQLStaff +
  @SQLCampaign +
  @SQLProject + 
  @SQLCountry +
  @SQLGuestType +
  @SQLPurpose +
  @SQLProducts + 
  @SQLWares +
  '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 ' + 
  '   AND ReservationAddressXTab.GuestNumber = MIS.GuestNumber ' + 
  '   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 +
  'WHERE 1=1 ' + @LF +
  'and StaffId not in (select * from #AutoUsers WITH (NOLOCK)) ' + @LF +
  'and ' + @DateLimitField + ' <= ''' + Convert(VarChar, DateAdd(yyyy, -1, @EndDate), 112) + ''' and ' + @DateLimitField + ' >= ''' + Convert(VarChar, DateAdd(yyyy, -1, @StartDate), 112) + ''' ' + @LF +
  'and ' + @DateLimitField + ' > ''' + Convert(VarChar, @SystemDateHist, 112) + ''' ' + @LF +
  @WhereStandard +
  'Group by  ' + @LF +
  @SQLTypeGroupingHist + @SQLDisplayTypeGrouping 
End

--*****************
--History, booked
--*****************
If (@DisplayType <> @DisplayTypeRes) --Reservations
   AND (@DisplayType <> @DisplayTypeWareCount)
   AND (NOT @TypeGroupingValue IN (@TypeGroupingStatus, @TypeGroupingStaff, @TypeGroupingCampaign, @TypeGroupingProject, @TypeGroupingCountry) )
   AND @SystemDateHistFromSP <> '20000101'
Begin
  If @TypeGroupingValue = @TypeGroupingPaxPerRoom
  Begin
	SET @SQLStatement3 = @LF + 'UNION ALL ' + @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 +
	'(SUM(Adult + Children1 + Children2) / IsNull(NULLIF(SUM(NumberOfRooms), 0), 1)) as PaxPerRoom, ' +
	'0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + --ActualHist, 12 fields
	'SUM(Adult) as BookedHistAdult, ' +
	'SUM(Children1) as BookedHistChildren1, ' +
	'SUM(Children2) as BookedHistChildren2, ' +
	'SUM(NumberOfRooms) as BookedHistNumberOfRooms, ' +
    '0, 0, 0, 0, 0, 0, 0, 0, ' + @LF +
    '''' + Convert(varchar, @SystemDateHistFromSP) + ''' as SystemDateHist ' + @LF +
	'FROM MISHistory WITH (NOLOCK) ' + @LF +
	'WHERE Adult + Children1 + Children2 > 0 ' +
    'and ValueType = 1 ' + @LF +
	' and SystemDate = ''' + Convert(varchar, @SystemDateHist, 23) + '''' + @LF +
	' and ' + @DateLimitField + ' <= ''' + Convert(varchar, DateAdd(yyyy, -1, @EndDate), 23) + ''' and ' + @DateLimitField + ' >= ''' + Convert(varchar, DateAdd(yyyy, -1, @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 ' + @LF + 
	'   (''' + @SelectRoomTypeNumbers + ''' = '''' or (Room_HallType in (SELECT No FROM #SelectRoomTypeNumbers) AND ValueType = 1)) ' + @LF + --mvtRooms
	'   AND ' + @LF +
	'    (''' + @SelectHallTypeNumbers + ''' = '''' or (Room_HallType 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 + ''' = '''' ' + 
	' and ''' + @SelectStatusNumbers + ''' = '''' ' + 
	' and ''' + @SelectCampaignCodeNumbers + ''' = '''' ' + 
	' and ''' + @SelectProjectCodeNumbers + ''' = '''' ' + @LF +
	'Group By ' + @LF +
	'(Adult + Children1 + Children2) / IsNull(NULLIF(NumberOfRooms, 0), 1) '
  End Else
  Begin
	SET @SQLStatement3 = @LF + 'UNION ALL ' + @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 +
	@SQLPOSHistBook +
	@SQLRoomType +
	@SQLValueType +
	@SQLHotelNumbersHistBook +
	'0, ' + --ReservationStatus,
	'0, ' + --StaffID,
	'0, ' + --CampaignCode,
	'0, ' + --ProjectCode,
	'0, ' + --CountryCode
	@SQLGuestTypeHistBook +
	@SQLPurposeHistBook +
	@SQLProductsHistBook +
	@SQLWaresHistBook +
	'0, ' + @LF + --PaxPerRoom
	'0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, ' + --ActualHist, 12 fields
	'SUM(Adult) as BookedHistAdult, ' +
	'SUM(Children1) as BookedHistChildren1, ' +
	'SUM(Children2) as BookedHistChildren2, ' +
	'SUM(NumberOfRooms) as BookedHistNumberOfRooms, ' +
	'SUM(Price) as BookedHistPrice, ' +
	'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 +
	'WHERE 1=1 ' + @LF +
	' and SystemDate = ''' + Convert(varchar, @SystemDateHist, 23) + '''' + @LF +
	' and ' + @DateLimitField + ' <= ''' + Convert(varchar, DateAdd(yyyy, -1, @EndDate), 23) + ''' and ' + @DateLimitField + ' >= ''' + Convert(varchar, DateAdd(yyyy, -1, @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 ' + @LF + 
	'   (''' + @SelectRoomTypeNumbers + ''' = '''' or (Room_HallType in (SELECT No FROM #SelectRoomTypeNumbers) AND ValueType = 1)) ' + @LF + --mvtRooms
	'   AND ' + @LF +
	'    (''' + @SelectHallTypeNumbers + ''' = '''' or (Room_HallType 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 + ''' = '''' ' + 
	' and ''' + @SelectStatusNumbers + ''' = '''' ' + 
	--' and ''' + @SelectStaffIDs + ''' = '''' ' + 
	' and ''' + @SelectCampaignCodeNumbers + ''' = '''' ' + 
	' and ''' + @SelectProjectCodeNumbers + ''' = '''' ' + @LF +
	'Group By ' + @LF +
	@SQLTypeGroupingHistBook + ', ValueType ' 
  End
	Print 'Booked history'
	Print '@SQLStatement3 = ' + @SQLStatement3
	

  if @IncludeExtraSale = 1
  Begin
    Print 'Extra (manual) sale last year'
	  Print '@SQLstatement4 = ' + @SQLstatement4 + @LF + @LF
    EXEC(@SQLStatement1 + @SQLStatement2 + @SQLStatement3 + ' UNION ALL ' + @SQLStatement4)
  End Else
  Begin
    Print 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
	EXEC(@SQLStatement1 + @SQLStatement2 + @SQLStatement3) 
  End
End Else
Begin
  if @IncludeExtraSale = 1
  Begin
    Print 'Extra (manual) sale last year'
    Print '@SQLstatement4 = ' + @SQLstatement4 + @LF + @LF
    EXEC(@SQLStatement1 + @SQLStatement2 + ' UNION ALL ' + @SQLStatement4)
  End Else
  Begin
    EXEC(@SQLStatement1 + @SQLStatement2)
  End
End

IF OBJECT_ID('#Refs', 'U') IS NOT NULL  
Begin
   DROP TABLE #Refs;  
End
DROP Table #SelectCountryNumbers
DROP Table #SelectSegmentNumbers
DROP Table #SelectPointOfSaleNumbers
DROP Table #SelectRoomTypeNumbers 
DROP Table #SelectHallTypeNumbers 
DROP Table #MISValueTypes
DROP Table #SelectHotelNumbers
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
