CREATE PROCEDURE [dbo].[HotelManagerSegments2]  @rundate char(8), @HotelNumberStr VARCHAR(100), @inclVat bit = 1, @inclPackage bit = 1, 
  @inclKorr bit = 0, @ooo bit = 0, @incldaymeeting bit = 0, @AReportOwnerReservation INT, @AInclNUL INT, @AInclEmpty INT, @AIncludeProvision BIT, @AIncludeAllotment BIT AS
SET NOCOUNT ON

/*
Remarks:
@AReportOwnerReservation: (0: rorExcluded, 1: rorOnly, 2: rorIncluded)  

Changes:
  KJ 20220506: Allotment added req #4059
  KJ 20200428: Ext: Added provision in MIS. Req #3788
  KJ 20180208: Danhostel Show occupied for singe segment in hotel manager report.
  KJ 20140128: Bug: Fix for "Amount for no Segment not shown". Case #CR068601
  KJ 20131126: Bug: Fix for "Today part is empty when showing HM report for a day in the future". Case #CR066877
  KJ 20130430: Ext: Added incl NUL and Empty products. Req #1530
  KJ 20120829: Ext: Removed VATFactor - not neede anymore
  KJ 20120828: Bug: Fix for "Year to date and month to date is wrong". Case #CR046249
  KJ 20120712: Ext: MISTableView rewrite
  KJ 20120709: Bug: MISTableView rewrite
  KJ 20120430: Ext: Added owner reservations.
  OF 20120131: Ext: MisTableView used instead of MisTable.
  KJ 20110801; Ext: Added Adults, Children1 and Children2. Req# 624
  KJ 20110226: Ext: Added VATFactor. Req# 826
  20090212: Bug: Fix for "Excel vat and excl package amount is wrong"
  20080723: Bug: Fix for "Remaining year+month not correct"
  20080722: Bug: Fix for "Month+Year budget should not include rundate"
  20080718: Bug: Fix for "year remaining numbers wrong."
  20080715: Handle for dates in the future
  20061124: Rewrite to Domino

*/

DECLARE @ShowPrint BIT
SET @ShowPrint = 0

DECLARE @Year CHAR(4)
DECLARE @Month CHAR(2)
DECLARE @SystemDate CHAR(8)

IF @rundate = ''
  SELECT @rundate = CONVERT(CHAR(8), CONVERT(DATETIME, systemdate, 112) - 1, 112) FROM system (NOLOCK)

SELECT @SystemDate = CONVERT(CHAR(8), CONVERT(DATETIME, systemdate, 112), 112) FROM system (NOLOCK)

SET @Year = SUBSTRING(@rundate, 1, 4) 
SET @month = SUBSTRING(@rundate, 5, 2)

IF @ShowPrint = 1
BEGIN
  PRINT 'RUNDate: ' + @rundate
  PRINT 'SystemDate: ' + @SystemDate
END

SET @AIncludeProvision = ISNULL(@AIncludeProvision, 0)

CREATE TABLE #HotelManagerSegment
(
  segmentname VARCHAR(100),
  segmentnumber INT, 

  todaynumberofrooms INT, 
  todaypax INT,
  todayAdults INT,
  todayChildren1 INT,
  todayChildren2 INT,
  todayprice MONEY,
  todayavgprice MONEY,

  monthnumberofrooms INT, 
  monthpax INT,
  monthAdults INT,
  monthChildren1 INT,
  monthChildren2 INT,
  monthprice MONEY,
  monthavgprice MONEY,

  yearnumberofrooms INT, 
  yearpax INT,
  yearAdults INT,
  yearChildren1 INT,
  yearChildren2 INT,
  yearprice MONEY,
  yearavgprice MONEY,
  
  monthbudgetnumberofrooms INT NULL, 
  monthbudgetprice MONEY NULL,
  monthbudgetavgprice MONEY NULL,

  yearbudgetnumberofrooms INT NULL, 
  yearbudgetprice MONEY NULL,
  yearbudgetavgprice MONEY NULL,

  ShowOccupied BIT NULL
)

DECLARE @segmentname varchar(25)
DECLARE @selectsegmentnumber INT         
DECLARE @segmentnumber INT         

DECLARE @todaynumberofrooms INT         
DECLARE @todaypax INT                   
DECLARE @todayAdults INT
DECLARE @todayChildren1 INT
DECLARE @todayChildren2 INT

DECLARE @TodayPrice MONEY
DECLARE @TodayPriceExPackage MONEY
DECLARE @TodayPriceExPackageExVat MONEY
DECLARE @TodayPriceInclPackageExVat MONEY
DECLARE @todayavgprice MONEY            

DECLARE @monthnumberofrooms INT         
DECLARE @monthpax INT
DECLARE @monthAdults INT
DECLARE @monthChildren1 INT
DECLARE @monthChildren2 INT
DECLARE @MonthPrice MONEY
DECLARE @MonthPriceExPackage MONEY
DECLARE @MonthPriceExPackageExVat MONEY
DECLARE @MonthPriceInclPackageExVat MONEY
DECLARE @monthavgprice MONEY            

DECLARE @monthnumberofroomsBooked INT         
DECLARE @monthpaxBooked INT
DECLARE @monthAdultsBooked INT
DECLARE @monthChildren1Booked INT
DECLARE @monthChildren2Booked INT
DECLARE @MonthPriceBooked MONEY
DECLARE @MonthPriceExPackageBooked MONEY
DECLARE @MonthPriceExPackageExVatBooked MONEY
DECLARE @MonthPriceInclPackageExVatBooked MONEY

DECLARE @yearnumberofrooms INT          
DECLARE @yearpax INT
DECLARE @yearAdults INT
DECLARE @yearChildren1 INT
DECLARE @yearChildren2 INT
DECLARE @YearPrice MONEY
DECLARE @YearPriceExPackage MONEY
DECLARE @YearPriceExPackageExVat MONEY
DECLARE @YearPriceInclPackageExVat MONEY
DECLARE @yearavgprice MONEY             

DECLARE @yearnumberofroomsBooked INT
DECLARE @yearpaxBooked INT
DECLARE @yearAdultsBooked INT
DECLARE @YearChildren1Booked INT
DECLARE @YearChildren2Booked INT
DECLARE @YearPriceBooked MONEY
DECLARE @YearPriceExPackageBooked MONEY
DECLARE @YearPriceExPackageExVatBooked MONEY
DECLARE @YearPriceInclPackageExVatBooked MONEY

DECLARE @monthbudgetnumberofrooms INT   
DECLARE @monthbudgetprice MONEY
DECLARE @monthbudgetPriceExPackage MONEY
DECLARE @monthbudgetPriceExPackageExVat MONEY
DECLARE @monthbudgetPriceInclPackageExVat MONEY
DECLARE @monthbudgetavgprice MONEY      

DECLARE @Actualmonthbudgetnumberofrooms INT   
DECLARE @Actualmonthbudgetprice MONEY
DECLARE @ActualmonthbudgetPriceExPackage MONEY
DECLARE @ActualmonthbudgetPriceExPackageExVat MONEY
DECLARE @ActualmonthbudgetPriceInclPackageExVat MONEY

DECLARE @yearbudgetnumberofrooms INT    
DECLARE @YearBudgetPrice MONEY
DECLARE @YearBudgetPriceExPackage MONEY
DECLARE @YearBudgetPriceExPackageExVat MONEY
DECLARE @YearBudgetPriceInclPackageExVat MONEY
DECLARE @yearbudgetavgprice MONEY       

DECLARE @Actualyearbudgetnumberofrooms INT    
DECLARE @ActualYearbudgetPrice MONEY
DECLARE @ActualYearbudgetPriceExPackage MONEY
DECLARE @ActualYearbudgetPriceExPackageExVat MONEY
DECLARE @ActualYearbudgetPriceInclPackageExVat MONEY

DECLARE @ShowOccupied BIT

DECLARE seg_cur CURSOR FOR
  SELECT text, number, ShowAsOccupiedInHM
  FROM segments (NOLOCK)
  UNION
  SELECT 'UNKNOWN', 0, 0
OPEN seg_cur

declare @valuetype varchar(1000) 
set @valuetype = '1'
if @incldaymeeting = 1 
  set @valuetype = @valuetype + ',3'
  
FETCH NEXT FROM seg_cur INTO @segmentname, @selectsegmentnumber, @Showoccupied

WHILE (@@fetch_status = 0)
BEGIN
  -- INIT PART - START
  IF @ShowPrint = 1
    print  'Start'

  SET @todaynumberofrooms = 0
  SET @todaypax = 0                  
  SET @todayAdults = 0
  SET @todayChildren1 = 0
  SET @todayChildren2 = 0
  SET @TodayPrice = 0
  SET @TodayPriceExPackage = 0
  SET @TodayPriceExPackageExVat = 0
  SET @TodayPriceInclPackageExVat = 0
  SET @todayavgprice = 0             

  SET @monthnumberofrooms = 0
  SET @monthpax = 0  
  SET @monthAdults = 0
  SET @monthChildren1 = 0
  SET @monthChildren2 = 0                
  SET @MonthPrice = 0
  SET @MonthPriceExPackage = 0
  SET @MonthPriceExPackageExVat = 0
  SET @MonthPriceInclPackageExVat = 0
  SET @monthavgprice = 0             

  SET @monthnumberofroomsBooked = 0
  SET @monthpaxBooked = 0
  SET @monthAdultsBooked = 0
  SET @monthChildren1Booked = 0
  SET @monthChildren2Booked = 0
  SET @MonthPriceBooked = 0
  SET @MonthPriceExPackageBooked = 0
  SET @MonthPriceExPackageExVatBooked = 0
  SET @MonthPriceInclPackageExVatBooked = 0

  SET @yearnumberofrooms = 0
  SET @yearpax = 0
  SET @yearAdults = 0
  SET @yearChildren1 = 0
  SET @yearChildren2 = 0
  SET @YearPrice = 0
  SET @YearPriceExPackage = 0
  SET @YearPriceExPackageExVat = 0
  SET @YearPriceInclPackageExVat = 0
  SET @yearavgprice = 0             

  SET @yearnumberofroomsBooked = 0
  SET @yearpaxBooked = 0
  SET @yearAdultsBooked = 0
  SET @YearChildren1Booked = 0
  SET @YearChildren2Booked = 0
  SET @YearPriceBooked = 0
  SET @YearPriceExPackageBooked = 0
  SET @YearPriceExPackageExVatBooked = 0
  SET @YearPriceInclPackageExVatBooked = 0

  SET @monthbudgetnumberofrooms = 0
  SET @monthbudgetPrice = 0
  SET @monthbudgetPriceExPackage = 0
  SET @monthbudgetPriceExPackageExVat = 0
  SET @monthbudgetPriceInclPackageExVat = 0
  SET @monthbudgetavgprice = 0
  
  SET @Actualmonthbudgetnumberofrooms = 0
  SET @ActualmonthbudgetPrice = 0
  SET @ActualmonthbudgetPriceExPackage = 0
  SET @ActualmonthbudgetPriceExPackageExVat = 0
  SET @ActualmonthbudgetPriceInclPackageExVat = 0

  SET @yearbudgetnumberofrooms = 0
  SET @YearBudgetPrice = 0
  SET @YearBudgetPriceExPackage = 0
  SET @YearBudgetPriceExPackageExVat = 0
  SET @YearBudgetPriceInclPackageExVat = 0
  SET @yearbudgetavgprice = 0

  SET @Actualyearbudgetnumberofrooms = 0
  SET @ActualYearBudgetPrice = 0
  SET @ActualYearBudgetPriceExPackage = 0
  SET @ActualYearBudgetPriceExPackageExVat = 0
  SET @ActualYearBudgetPriceInclPackageExVat = 0
  -- INIT PART - END

  -- TODAY PART - START
  SELECT @segmentnumber = segmentnumber, @todaynumberofrooms = sum(actualnumberofrooms) + sum(Bookednumberofrooms), @todaypax = sum(actualadult+actualchildren1+actualchildren2) +sum(Bookedadult+Bookedchildren1+Bookedchildren2), @todayAdults = sum(actualadult) + sum(BookedAdult), @todayChildren1 = SUM(ActualChildren1) + SUM(BookedChildren1), @todayChildren2 = SUM(ActualChildren2) + SUM(BookedChildren2), 
  @TodayPrice = sum(ActualPriceInclPackage) + sum(BookedPriceInclPackage), @TodayPriceExPackage = sum(ActualPriceExPackage) + sum(BookedPriceExPackage), @TodayPriceExPackageExVat = sum(ActualPriceExPackageExVat) + sum(BookedPriceExPackageExVat), @TodayPriceInclPackageExVat = sum(ActualPriceInclPackageExVat) + sum(BookedPriceInclPackageExVat)
  from MisTableView WITH (nolock) where datestamp = @rundate and segmentnumber = @selectsegmentnumber and valuetype in (select value from dbo.SplitNumberList(@valuetype))  and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) 
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  group by segmentnumber 

  -- MONTH PART - START
  SELECT @segmentnumber = segmentnumber, @monthnumberofrooms = sum(actualnumberofrooms), @monthpax = sum(actualadult+actualchildren1+actualchildren2), @monthAdults = SUM(ActualAdult), @monthChildren1 = SUM(ActualChildren1), @monthChildren2 = SUM(ActualChildren2), 
    @MonthPrice = sum(ActualPriceInclPackage), @MonthPriceExPackage = sum(ActualPriceExPackage), @MonthPriceExPackageExVat = sum(ActualPriceExPackageExVat), @MonthPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
  from MisTableView WITH (nolock) where datestamp like @year + @month + '%' and datestamp <= @rundate and segmentnumber = @selectsegmentnumber and valuetype in (select value from dbo.SplitNumberList(@valuetype))and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) 
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  group by segmentnumber 

  IF @rundate >= @SystemDate
  BEGIN
    SELECT @monthnumberofroomsBooked = sum(Bookednumberofrooms), @monthpaxBooked = sum(Bookedadult+Bookedchildren1+Bookedchildren2), @monthAdultsBooked = SUM(BookedAdult), @monthChildren1Booked = SUM(BookedChildren1), @monthChildren2Booked = SUM(BookedChildren2), 
      @MonthPriceBooked = sum(BookedPriceInclPackage), @MonthPriceExPackageBooked = sum(BookedPriceExPackage), @MonthPriceExPackageExVatBooked = sum(BookedPriceExPackageExVat), @MonthPriceInclPackageExVatBooked = sum(BookedPriceInclPackageExVat)
    from MisTableView WITH (nolock) where datestamp like @year + @month + '%' and datestamp <= @rundate and segmentnumber = @selectsegmentnumber and valuetype in (select value from dbo.SplitNumberList(@valuetype))and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) 
    AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
    AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
    AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
    group by segmentnumber 

    SET @monthnumberofrooms = ISNULL(@monthnumberofrooms, 0) + ISNULL(@monthnumberofroomsBooked, 0)
    SET @monthpax = ISNULL(@monthpax, 0) + ISNULL(@monthpaxBooked, 0)
    SET @monthAdults = ISNULL(@monthAdults, 0) + ISNULL(@monthAdultsBooked, 0)
    SET @monthChildren1 = ISNULL(@monthChildren1, 0) + ISNULL(@monthChildren1Booked, 0)
    SET @monthChildren2 = ISNULL(@monthChildren2, 0) + ISNULL(@monthChildren2Booked, 0)
    SET @monthprice = ISNULL(@monthprice, 0) + ISNULL(@monthpriceBooked, 0)
    SET @MonthPriceExPackage = ISNULL(@MonthPriceExPackage, 0) + ISNULL(@MonthPriceExPackageBooked, 0)
    SET @MonthPriceExPackageExVat = ISNULL(@MonthPriceExPackageExVat, 0) + ISNULL(@MonthPriceExPackageExVatBooked, 0)
    SET @MonthPriceInclPackageExVat = ISNULL(@MonthPriceInclPackageExVat, 0) + ISNULL(@MonthPriceInclPackageExVatBooked, 0)
  END

  -- YEAR PART - START
  SELECT @segmentnumber = segmentnumber, @yearnumberofrooms = sum(actualnumberofrooms), @yearpax = sum(actualadult+actualchildren1+actualchildren2), @yearAdults = SUM(ActualAdult), @yearChildren1 = SUM(ActualChildren1), @yearChildren2 = SUM(ActualChildren2), 
    @YearPrice = sum(ActualPriceInclPackage), @YearPriceExPackage = sum(ActualPriceExPackage), @YearPriceExPackageExVat = sum(ActualPriceExPackageExVat), @YearPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
  from MisTableView WITH (nolock) where datestamp like @year + '%' and datestamp <= @rundate and segmentnumber = @selectsegmentnumber and valuetype in (select value from dbo.SplitNumberList(@valuetype))and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) 
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  group by segmentnumber   
    
  IF @rundate >= @SystemDate
  BEGIN
    SELECT @YearnumberofroomsBooked = sum(Bookednumberofrooms), @YearpaxBooked = sum(Bookedadult+Bookedchildren1+Bookedchildren2), @YearAdultsBooked = SUM(BookedAdult), @YearChildren1Booked = SUM(BookedChildren1), @YearChildren2Booked = SUM(BookedChildren2), 
      @YearPriceBooked = sum(BookedPriceInclPackage), @YearPriceExPackageBooked = sum(BookedPriceExPackage), @YearPriceExPackageExVatBooked = sum(BookedPriceExPackageExVat), @YearPriceInclPackageExVatBooked = sum(BookedPriceInclPackageExVat)
    from MisTableView WITH (nolock) where datestamp like @year + '%' and datestamp <= @rundate and segmentnumber = @selectsegmentnumber and valuetype in (select value from dbo.SplitNumberList(@valuetype))and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) 
    AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
    AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
    AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
    group by segmentnumber 

    SET @Yearnumberofrooms = @Yearnumberofrooms + ISNULL(@yearnumberofroomsBooked, 0)
    SET @yearpax = @Yearpax + ISNULL(@YearpaxBooked, 0)
    SET @YearAdults = ISNULL(@YearAdults, 0) + ISNULL(@YearAdultsBooked, 0)
    SET @YearChildren1 = ISNULL(@YearChildren1, 0) + ISNULL(@YearChildren1Booked, 0)
    SET @YearChildren2 = ISNULL(@YearChildren2, 0) + ISNULL(@YearChildren2Booked, 0)
    SET @Yearprice = ISNULL(@Yearprice, 0) + ISNULL(@YearpriceBooked, 0)
    SET @YearPriceExPackage = ISNULL(@YearPriceExPackage, 0) + ISNULL(@YearPriceExPackageBooked, 0)
    SET @YearPriceExPackageExVat = ISNULL(@YearPriceExPackageExVat, 0) + ISNULL(@YearPriceExPackageExVatBooked, 0)
    SET @YearPriceInclPackageExVat = ISNULL(@YearPriceInclPackageExVat, 0) + ISNULL(@YearPriceInclPackageExVatBooked, 0)
  END

  -------------------------------
  -- BUDGET PART - START
  -------------------------------
  -- MONTH BUDGET PART - START
  SELECT @segmentnumber = segmentnumber, @monthbudgetnumberofrooms = sum(bookednumberofrooms), 
    @monthbudgetPrice = sum(BookedPriceInclPackage), @monthbudgetPriceExPackage = sum(BookedPriceExPackage), @monthbudgetPriceExPackageExVat = sum(BookedPriceExPackageExVat), @monthbudgetPriceInclPackageExVat = sum(BookedPriceInclPackageExVat)
  from MisTableView (nolock) where datestamp like @year + @month + '%'  and datestamp > @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) and segmentnumber = @selectsegmentnumber 
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  group by segmentnumber
    
  IF @rundate < @SystemDate
  BEGIN
    SELECT @segmentnumber = segmentnumber, @Actualmonthbudgetnumberofrooms = sum(Actualnumberofrooms), 
      @ActualmonthbudgetPrice = sum(ActualPriceInclPackage), @ActualmonthbudgetPriceExPackage = sum(ActualPriceExPackage), @ActualmonthbudgetPriceExPackageExVat = sum(ActualPriceExPackageExVat), @ActualmonthbudgetPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
    from MisTableView (nolock) where datestamp like @year + @month + '%'  and datestamp > @rundate and valuetype in (select value from dbo.SplitNumberList(@valuetype)) and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) and segmentnumber = @selectsegmentnumber 
    AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
    AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
    AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
    group by segmentnumber

    SET @monthbudgetnumberofrooms = @monthbudgetnumberofrooms + ISNULL(@Actualmonthbudgetnumberofrooms, 0)
    SET @monthbudgetprice = ISNULL(@monthbudgetprice, 0) + ISNULL(@Actualmonthbudgetprice, 0)
    SET @monthbudgetPriceExPackage = ISNULL(@monthbudgetpriceExPackage, 0) + ISNULL(@ActualmonthbudgetpriceExPackage, 0)
    SET @monthbudgetPriceExPackageExVat = ISNULL(@monthbudgetpriceExPackageExVat, 0) + ISNULL(@ActualmonthbudgetpriceExPackageExVat, 0)
    SET @monthbudgetPriceInclPackageExVat = ISNULL(@monthbudgetpriceInclPackageExVat, 0) + ISNULL(@ActualmonthbudgetpriceInclPackageExVat, 0)
  END

  -- YEAR BUDGET PART - START
  SELECT @segmentnumber = segmentnumber, @yearbudgetnumberofrooms = sum(bookednumberofrooms), 
    @YearbudgetPrice = sum(BookedPriceInclPackage), @YearbudgetPriceExPackage = sum(BookedPriceExPackage), @YearbudgetPriceExPackageExVat = sum(BookedPriceExPackageExVat), @YearbudgetPriceInclPackageExVat = sum(BookedPriceInclPackageExVat)
  from MisTableView (nolock) where datestamp like @year + '%'  and datestamp > @rundate  and valuetype in (select value from dbo.SplitNumberList(@valuetype)) and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) and segmentnumber = @selectsegmentnumber 
  AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
  AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
  AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
  group by segmentnumber
    
  IF @rundate < @SystemDate
  BEGIN
    SELECT @segmentnumber = segmentnumber, @Actualyearbudgetnumberofrooms = sum(Actualnumberofrooms), 
    @ActualYearbudgetPrice = sum(ActualPriceInclPackage), @ActualYearbudgetPriceExPackage = sum(ActualPriceExPackage), @ActualYearbudgetPriceExPackageExVat = sum(ActualPriceExPackageExVat), @ActualYearbudgetPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
    from MisTableView (nolock) where datestamp like @year + '%'  and datestamp > @rundate  and valuetype in (select value from dbo.SplitNumberList(@valuetype)) and HotelCustomerNumber IN (SELECT DISTINCT CustomerNumber FROM DBO.HotelCustomerNumbersFromHotelNumbers(@HotelNumberStr)) and segmentnumber = @selectsegmentnumber 
    AND (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
    AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
    AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
    AND (@AIncludeProvision = 1 OR ISNULL(WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18))
    AND (@AIncludeAllotment = 1 OR MISTableView.Room_HallType <> 2)
    group by segmentnumber

    SET @yearbudgetnumberofrooms = @yearbudgetnumberofrooms + ISNULL(@Actualyearbudgetnumberofrooms, 0)
    SET @Yearbudgetprice = ISNULL(@Yearbudgetprice, 0) + ISNULL(@ActualYearbudgetprice, 0)
    SET @YearbudgetPriceExPackage = ISNULL(@YearbudgetpriceExPackage, 0) + ISNULL(@ActualYearbudgetpriceExPackage, 0)
    SET @YearbudgetPriceExPackageExVat = ISNULL(@YearbudgetpriceExPackageExVat, 0) + ISNULL(@ActualYearbudgetpriceExPackageExVat, 0)
    SET @YearbudgetPriceInclPackageExVat = ISNULL(@YearbudgetpriceInclPackageExVat, 0) + ISNULL(@ActualYearbudgetpriceInclPackageExVat, 0)
  END
    
  -- CALC PART - START
  IF @inclVAT = 1 AND @inclPackage = 0
  BEGIN
    SET @todayPrice = @TodayPriceExPackage
    SET @monthPrice = @MonthPriceExPackage
    SET @yearPrice  = @YearPriceExPackage
    SET @monthbudgetPrice = @MonthBudgetPriceExPackage
    SET @yearbudgetPrice  = @YearBudgetPriceExPackage  
  END
  ELSE IF @inclVAT = 0 AND @inclPackage = 0
  BEGIN
    SET @todayPrice = @TodayPriceExPackageExVat
    SET @monthPrice = @MonthPriceExPackageExVat
    SET @yearPrice  = @YearPriceExPackageExVat
    SET @monthbudgetPrice = @MonthBudgetPriceExPackageExVat
    SET @yearbudgetPrice  = @YearBudgetPriceExPackageExVat
  END
  ELSE IF @inclVAT = 0 AND @inclPackage = 1
  BEGIN
    SET @todayPrice = @TodayPriceInclPackageExVat
    SET @monthPrice = @MonthPriceInclPackageExVat
    SET @yearPrice  = @YearPriceInclPackageExVat
    SET @monthbudgetPrice = @MonthBudgetPriceInclPackageExVat
    SET @yearbudgetPrice  = @YearBudgetPriceInclPackageExVat
  END

  IF (@todayprice > 0) AND (@todaynumberofrooms > 0)
    SET @todayavgprice = (isnull(@todayprice, 0)) / isnull(@todaynumberofrooms, 0)
  IF (@monthprice > 0) AND (@monthnumberofrooms > 0)
    SET @monthavgprice = (isnull(@monthprice, 0)) / isnull(@monthnumberofrooms, 0)
  IF (@yearprice > 0) AND (@yearnumberofrooms > 0)
    SET @yearavgprice = (isnull(@yearprice, 0)) / isnull(@yearnumberofrooms, 0)
  IF (@monthbudgetprice > 0) AND (@monthbudgetnumberofrooms > 0)
    SET @monthbudgetavgprice = (isnull(@monthbudgetprice, 0)) / isnull(@monthbudgetnumberofrooms, 0)
  IF (@yearbudgetprice > 0) AND (@yearbudgetnumberofrooms > 0)
    SET @yearbudgetavgprice = (isnull(@yearbudgetprice, 0)) / isnull(@yearbudgetnumberofrooms, 0)
  
  -- INSERT PART - START
  INSERT INTO #HotelManagerSegment (segmentname, segmentnumber, todaynumberofrooms, todaypax, todayAdults, todayChildren1, todayChildren2, todayprice, todayavgprice, monthnumberofrooms, monthpax, monthAdults, monthChildren1, monthChildren2, monthprice, monthavgprice, yearnumberofrooms, yearpax, yearAdults, yearChildren1, yearChildren2, yearprice, yearavgprice, monthbudgetnumberofrooms, monthbudgetprice, monthbudgetavgprice, yearbudgetnumberofrooms, yearbudgetprice, yearbudgetavgprice, ShowOccupied) 
  VALUES (@segmentname, @selectsegmentnumber, @todaynumberofrooms, @todaypax, @todayAdults, @todayChildren1, @todayChildren2, @todayprice, @todayavgprice, @monthnumberofrooms, @monthpax, @monthAdults, @monthChildren1, @monthChildren2, @monthprice, @monthavgprice, @yearnumberofrooms, @yearpax, @yearAdults, @yearChildren1, @yearChildren2, @yearprice, @yearavgprice, @monthbudgetnumberofrooms, @monthbudgetprice, isnull(@monthbudgetavgprice, 0), @yearbudgetnumberofrooms, @yearbudgetprice, isnull(@yearbudgetavgprice, 0), @ShowOccupied)

  FETCH NEXT FROM seg_cur INTO @segmentname, @selectsegmentnumber, @Showoccupied
END

CLOSE seg_cur
DEALLOCATE seg_cur

SELECT * FROM #HotelManagerSegment (NOLOCK)

DROP TABLE #HotelManagerSegment