CREATE PROCEDURE dbo.HotelmanagerReservations @rundate VARCHAR(8), @HotelNumberStr VARCHAR(100), @ooo BIT = -1, @includedaymeeting BIT = 0, 
  @InclVat bit = 1, @InclPackage bit = 1, @AReportOwnerReservation INT, @AInclNUL BIT, @AInclEmpty BIT, @AAdditionalTO MONEY = 0, @AWorkHour INT = 0 AS
SET NOCOUNT ON

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

  Changes:
    KJ 20151021: Bug: Fix for "Wrong type of input params"
    KJ 20151012: Ext: Added Turnover per hour
    KJ 20130430: Ext: Added incl NUL and Empty products. Req #1530
    KJ 20120829: Ext: Removed VATFactor - not neede anymore
    KJ 20120712: Bug: MISTableView rewrite
    KJ 20120709: Bug: MISTableView rewrite
    KJ 20120430: Ext: Added owner reservations.
    KJ 20120216: Ext: Use MISTableView to select data.
    KJ 20110801; Ext: Added Adults, Children1 and Children2. Req# 624
    KJ 20110615: Bug: Fix for "Excl vat and excl package amount is wrong". Case# CR024613
    KJ 20110226: Ext: Added VATFactor. Req# 826
    KJ 20091207: Bug: Fix for "Rev/par not correct when out of order rooms"
    KJ 20090406: Ext: Added use of new sp -> HotelManagerNumberOfRooms
    KJ 20080922: Bug: Fix for "Divide by zero"
    KJ 20080723: Ext: Added support for date in the future
    KJ 20080107: Bug Fix for "Ambigeus fieldname 'Type'"
    KJ 20070309: Added early departures
    KJ 20070227: Number of Out of order not correct.
    KJ 20061124: Added Rev/Par
    KJ 20061124: Rewrite to Domino
    KJ 20060905: Wrong NumberOfDayRooms
    KJ 20060905: TodayNumberOfRooms wrong
    KJ 20060816: Also show noshows for guestnumber > 0
    KJ 20060814: Only Include OutOfOrder to belęg procent if selected
    KJ 20060814: Do not include Dayrooms in Belęg Procent. 
    KJ 20060707: Using DBO.DaysInMonth to get number of days in month
    KJ 20050816: Number of DayRooms wrong
    KJ 20050721: Added number of Upgrade rooms
    KJ 20050720: Added output of Dayrooms
*/

declare @valuetype varchar(10) 
set @valuetype = '1'
if @includedaymeeting = 1
  set @valuetype = @valuetype + ',3'

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

IF @rundate = '' 
  SELECT @rundate = CONVERT(VARCHAR(8), CONVERT(DATETIME, SystemDate, 112) - 1, 112) FROM system WITH (NOLOCK)

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

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

DECLARE @dayofmonth INT 
DECLARE @dayofyear INT 
DECLARE @Remainingdayofmonth INT 
DECLARE @Remainingdayofyear INT 

SET @dayofmonth = CONVERT(INT, SUBSTRING(@rundate, 7, 2))
SET @dayofyear = DATEPART(dayofyear, CONVERT(DATETIME, @rundate, 112))

DECLARE @totalroom INT
SELECT @TotalRoom = COUNT(Number) FROM Rooms WITH (NOLOCK) WHERE HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))

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

DECLARE @MonthPrice MONEY
DECLARE @MonthPriceExPackage MONEY
DECLARE @MonthPriceExPackageExVat MONEY
DECLARE @MonthPriceInclPackageExVat MONEY

DECLARE @RemainingMonthPrice MONEY
DECLARE @RemainingMonthPriceExPackage MONEY
DECLARE @RemainingMonthPriceExPackageExVat MONEY
DECLARE @RemainingMonthPriceInclPackageExVat MONEY

DECLARE @RemainingMonthPriceBooked MONEY
DECLARE @RemainingMonthPriceExPackageBooked MONEY
DECLARE @RemainingMonthPriceExPackageExVatBooked MONEY
DECLARE @RemainingMonthPriceInclPackageExVatBooked MONEY

DECLARE @YearPrice MONEY
DECLARE @YearPriceExPackage MONEY
DECLARE @YearPriceExPackageExVat MONEY
DECLARE @YearPriceInclPackageExVat MONEY

DECLARE @RemainingYearPrice MONEY
DECLARE @RemainingYearPriceExPackage MONEY
DECLARE @RemainingYearPriceExPackageExVat MONEY
DECLARE @RemainingYearPriceInclPackageExVat MONEY

DECLARE @RemainingYearPriceBooked MONEY
DECLARE @RemainingYearPriceExPackageBooked MONEY
DECLARE @RemainingYearPriceExPackageExVatBooked MONEY
DECLARE @RemainingYearPriceInclPackageExVatBooked MONEY

--------------------------------------------------------------------------------------
-------------------  TODAY PART START  -----------------------------------------------
--------------------------------------------------------------------------------------
--------------------  TODAY WALKIN - START  ------------------------------------------
declare @todaynumberofromswalkin  int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 2, 1, 0, @todaynumberofromswalkin OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  TODAY WALKIN - END  -----------------------------------------
--------------------  TODAY Early departures - START  -----------------------------
DECLARE @TodayEarlyDeparture INT
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 6, 1, 0, @TodayEarlyDeparture OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  TODAY Early departures - END  -------------------------------
--------------------  TODAY INHOUSE - START  -----------------------------------------
declare @todaynumberofrooms int
IF @rundate <= @SystemDate
  SELECT @todaynumberofrooms = sum(actualnumberofrooms) 
  from mistableView WITH (nolock) 
  where datestamp = @rundate 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)
ELSE 
  SELECT @todaynumberofrooms = sum(Bookednumberofrooms) 
  from mistableView WITH (nolock) 
  where datestamp = @rundate 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)

--------------------  TODAY INHOUSE - END  -----------------------------------------
--------------------  TODAY ARRIVALS - START  -----------------------------------------
declare @todaynumberofroomsarrivals  int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 1, 1, 0, @todaynumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  TODAY ARRIVALS - END  -----------------------------------------
--------------------  TODAY DAYROOM - START  -----------------------------------------
declare @todaynumberofdayrooms  int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 4, 1, 0, @todaynumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  TODAY DAYROOM - END  -----------------------------------------
--------------------  TODAY OUT OF ORDER - START  ------------------------------------
declare @todayoutoforder  int

SELECT @todayoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate < @rundate THEN @rundate ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate > @rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr='' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@rundate

--------------------  TODAY OUT OF ORDER - END  ------------------------------------
--------------------  TODAY NOSHOW - START  ------------------------------------
declare @todaynoshow  int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 3, 1, 0, @todaynoshow OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  TODAY NOSHOW - END  ------------------------------------
--------------------  TODAY Upgrade rooms - START  -----------------------------------------
declare @TodayNumberOfRoomsUpgrade int
EXEC HotelManagerNumberOfRooms @rundate, '', '', @HotelNumberStr, 5, 1, 0, @TodayNumberOfRoomsUpgrade OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  TODAY Upgrade rooms - END  -----------------------------------------
--------------------  TODAY Rev/Par - START  ---------------------------------------------
SELECT @TodayPrice = sum(ActualPriceInclPackage), @TodayPriceExPackage = sum(ActualPriceExPackage), @TodayPriceExPackageExVat = sum(ActualPriceExPackageExVat), @TodayPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
from mistableView WITH (nolock) where datestamp = @rundate and valuetype = 1 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)
--------------------  TODAY Rev/Par - END  ---------------------------------------------

--------------------------------------------------------------------------------------
-------------------  TODAY PART END  -------------------------------------------------
--------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
-------------------  MONTH PART START  -----------------------------------------------
--------------------------------------------------------------------------------------
--------------------  MONTH WALKIN - START  -----------------------------------------
declare @monthnumberofromswalkin  int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 2, 1, 0, @monthnumberofromswalkin OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  MONTH WALKIN - END  -----------------------------------------
--------------------  MONTH Early departures - START  -----------------------------

DECLARE @MonthEarlyDeparture INT
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 6, 1, 0, @MonthEarlyDeparture OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  MONTH Early departures - END  -------------------------------
--------------------  MONTH INHOUSE - START  --------------------------------------
declare @monthnumberofrooms  int
declare @monthnumberofroomsBooked INT
select @monthnumberofrooms = sum(ActualNumberOfRooms) 
from mistableView WITH (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 (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
 
IF @rundate >= @SystemDate
BEGIN
  select @monthnumberofroomsBooked = sum(BookedNumberOfRooms) 
  from mistableView WITH (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 (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
    
  SET @monthnumberofrooms = ISNULL(@monthnumberofrooms, 0) + ISNULL(@monthnumberofroomsBooked, 0)
END

--------------------  MONTH INHOUSE - END  -----------------------------------------
--------------------  MONTH ARRIVALS - START  --------------------------------------
declare @monthnumberofroomsarrivals  int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 1, 1, 0, @monthnumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  MONTH ARRIVALS - END  -----------------------------------------
--------------------  MONTH DAYROOM - START  ----------------------------------------
declare @monthnumberofdayrooms  int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 4, 1, 0, @monthnumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  MONTH DAYROOM - END  ---------------------------------------
--------------------  MONTH OUT OF ORDER - START  ------------------------------
declare @monthoutoforder  int
SELECT @monthoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + @Month + '01' THEN @Year + @Month + '01' ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@Year + @Month + '01'

--------------------  MONTH OUT OF ORDER - END  --------------------------------
--------------------  MONTH NOSHOW - START  ------------------------------------
declare @monthnoshow  int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 3, 1, 0, @monthnoshow OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  MONTH NOSHOW - END  ------------------------------------
--------------------  MONTH upgrade rooms - START  ---------------------------
declare @MonthNumberOfRoomsUpgrade int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 5, 1, 0, @MonthNumberOfRoomsUpgrade OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  MONTH Upgrade rooms - END  -----------------------------------
--------------------  MONTH Rev/Par - START  ---------------------------------------------

SELECT @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 valuetype = 1 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)

------------------------------------------------------------------------------------
-------------------  MONTH PART END  -----------------------------------------------
------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
-------------------  Remaining MONTH PART START  -------------------------------------
--------------------------------------------------------------------------------------
--------------------  Remaining MONTH INHOUSE - START  --------------------------------------
declare @Remainingmonthnumberofrooms  int
declare @RemainingmonthnumberofroomsBooked INT
DECLARE @RemainingmonthPax INT
DECLARE @RemainingMonthAdults INT
DECLARE @RemainingMonthChildren1 INT
DECLARE @RemainingMonthChildren2 INT
DECLARE @RemainingmonthPaxBooked INT
DECLARE @RemainingMonthAdultsBooked INT
DECLARE @RemainingMonthChildren1Booked INT
DECLARE @RemainingMonthChildren2Booked INT

select @Remainingmonthnumberofrooms = sum(ActualNumberOfRooms), 
  @RemainingmonthPax = sum(actualadult+actualchildren1+actualchildren2),
  @RemainingMonthAdults = sum(actualadult),
  @RemainingMonthChildren1 = SUM(ActualChildren1),
  @RemainingMonthChildren2 = SUM(ActualChildren2),
  @RemainingMonthPrice = sum(ActualPriceInclPackage), 
  @RemainingMonthPriceExPackage = sum(ActualPriceExPackage), 
  @RemainingMonthPriceExPackageExVat = sum(ActualPriceExPackageExVat), 
  @RemainingMonthPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
from mistableView WITH (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 (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
 
IF @rundate < @SystemDate
BEGIN
  select @RemainingmonthnumberofroomsBooked = sum(BookedNumberOfRooms), 
    @RemainingmonthPaxBooked = sum(Bookedadult+Bookedchildren1+Bookedchildren2),
    @RemainingMonthAdultsBooked = SUM(BookedAdult),
    @RemainingMonthChildren1Booked = SUM(BookedChildren1),
    @RemainingMonthChildren2Booked = SUM(BookedChildren2),
    @RemainingMonthPriceBooked = sum(BookedPriceInclPackage), 
    @RemainingMonthPriceExPackageBooked = sum(BookedPriceExPackage), 
    @RemainingMonthPriceExPackageExVatBooked = sum(BookedPriceExPackageExVat), 
    @RemainingMonthPriceInclPackageExVatBooked = sum(BookedPriceInclPackageExVat)
  from mistableView WITH (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 (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)

  SET @Remainingmonthnumberofrooms = ISNULL(@Remainingmonthnumberofrooms, 0) + ISNULL(@RemainingmonthnumberofroomsBooked, 0)
  SET @RemainingmonthPax = ISNULL(@RemainingmonthPax, 0) + ISNULL(@RemainingmonthPaxBooked, 0)
  SET @RemainingMonthAdults = ISNULL(@RemainingmonthAdults, 0) + ISNULL(@RemainingmonthAdultsBooked, 0)
  SET @RemainingMonthChildren1 = ISNULL(@RemainingmonthChildren1, 0) + ISNULL(@RemainingmonthChildren1Booked, 0)
  SET @RemainingMonthChildren2 = ISNULL(@RemainingmonthChildren2, 0) + ISNULL(@RemainingmonthChildren2Booked, 0)
  SET @RemainingMonthPrice = ISNULL(@RemainingMonthPrice, 0) + ISNULL(@RemainingMonthPriceBooked, 0)
  SET @RemainingMonthPriceExPackage = ISNULL(@RemainingMonthPriceExPackage, 0) + ISNULL(@RemainingMonthPriceExPackageBooked, 0)
  SET @RemainingMonthPriceExPackageExVat = ISNULL(@RemainingMonthPriceExPackageExVat, 0) + ISNULL(@RemainingMonthPriceExPackageExVatBooked, 0)
  SET @RemainingMonthPriceInclPackageExVat = ISNULL(@RemainingMonthPriceInclPackageExVat, 0) + ISNULL(@RemainingMonthPriceInclPackageExVatBooked, 0)
END

-------------------- Remaining MONTH INHOUSE - END  -----------------------------------------
-------------------- Remaining MONTH ARRIVALS - START  --------------------------------------
declare @Remainingmonthnumberofroomsarrivals  int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 1, 1, 1, @Remainingmonthnumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

-------------------- Remaining MONTH ARRIVALS - END  -----------------------------------------
-------------------- Remaining MONTH DAYROOM - START  ----------------------------------------
declare @Remainingmonthnumberofdayrooms int
EXEC HotelManagerNumberOfRooms @rundate, @year, @month, @HotelNumberStr, 4, 1, 1, @Remainingmonthnumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

-------------------- Remaining MONTH DAYROOM - END  ---------------------------------------
-------------------- Remaining MONTH OUT OF ORDER - START  ------------------------------
declare @Remainingmonthoutoforder  int
SELECT @Remainingmonthoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + @Month + '01' THEN @Year + @Month + '01' ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate>@rundate AND DepartureDate>@Year + @Month + '01'

-------------------- Remaining MONTH OUT OF ORDER - END  --------------------------------
-------------------- Remaining MONTH upgrade rooms - START  ---------------------------
declare @RemainingMonthNumberOfRoomsUpgrade int
IF @HotelNumberStr <> ''
BEGIN
  SELECT DISTINCT @RemainingMonthNumberOfRoomsUpgrade = count(*)
  FROM RESERVATIONROOMLINE WITH (NOLOCK) 
  INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 
  INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
  INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
  LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
  WHERE (1=1) 
  AND (((
  ((convert(varchar(8), RESERVATIONROOMLINE.DepartureDate, 112) like @year + @month + '%') AND (RESERVATIONROOMLINE.ArrivalDate > @RunDate) AND (Reservations.GuestNumber = 0)) 
  OR  ((convert(varchar(8), RESERVATIONS.DepartureDate, 112) like @year + @month + '%') AND (RESERVATIONS.ArrivalDate > @RunDate)) AND (Reservations.GuestNumber > 0))) 
  AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
   ) 
  AND ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))
  AND Reservations.Referencenumber > 0
  AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
END
ELSE
BEGIN
  SELECT DISTINCT @RemainingMonthNumberOfRoomsUpgrade = count(*)
  FROM RESERVATIONROOMLINE WITH (NOLOCK) 
  INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 

  INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
  INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
  LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
  WHERE (1=1) 
  AND (((
  ((convert(varchar(8), RESERVATIONROOMLINE.DepartureDate, 112) like @year + @month + '%') AND (RESERVATIONROOMLINE.ArrivalDate > @RunDate) AND (Reservations.GuestNumber = 0)) 
  OR  ((convert(varchar(8), RESERVATIONS.DepartureDate, 112) like @year + @month + '%') AND (RESERVATIONS.ArrivalDate > @RunDate)) AND (Reservations.GuestNumber > 0))) 
  AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
   ) 
  AND Reservations.Referencenumber > 0
  AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
END

-------------------- Remaining MONTH Upgrade rooms - END  -----------------------------------
------------------------------------------------------------------------------------
------------------- Remaining MONTH PART END  -----------------------------------------------
------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
-------------------  YEAR PART START  -----------------------------------------------
-------------------------------------------------------------------------------------
--------------------  YEAR WALKIN - START  ------------------------------------------
declare @yearnumberofromswalkin  int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 2, 1, 0, @yearnumberofromswalkin OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  YEAR WALKIN - END  -----------------------------------------
--------------------  YEAR Early departures - START  -----------------------------

DECLARE @YearEarlyDeparture INT
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 6, 1, 0, @YearEarlyDeparture OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  YEAR Early departures - END  -------------------------------
--------------------  YEAR INHOUSE - START  --------------------------------------
declare @yearnumberofrooms  int
select @yearnumberofrooms = sum(ActualNumberOfRooms) 
from mistableView WITH (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 (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)

--------------------  YEAR INHOUSE - END  -----------------------------------------
--------------------  YEAR ARRIVALS - START  --------------------------------------
declare @yearnumberofroomsarrivals  int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 1, 1, 0, @yearnumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  YEAR ARRIVALS - END  -----------------------------------------
--------------------  YEAR DAYROOM - START  ----------------------------------------
declare @yearnumberofdayrooms  int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 4, 1, 0, @yearnumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  YEAR DAYROOM - END  ---------------------------------------
--------------------  YEAR OUT OF ORDER - START  ------------------------------
declare @yearoutoforder  int
SELECT @yearoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + '0101' THEN @Year + '0101' ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate<=@rundate AND DepartureDate>@Year + '0101'

--------------------  YEAR OUT OF ORDER - END  --------------------------------
--------------------  YEAR NOSHOW - START  ------------------------------------
declare @yearnoshow  int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 3, 1, 0, @yearnoshow OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

--------------------  YEAR NOSHOW - END  ------------------------------------
--------------------  YEAR upgrade rooms - START  ---------------------------
declare @YearNumberOfRoomsUpgrade int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 5, 1, 0, @YearNumberOfRoomsUpgrade OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

-------------------- Year Upgrade rooms - END  ------------------------------------
--------------------  YEAR Rev/Par - START  ---------------------------------------
SELECT @YearPrice = sum(ActualPriceInclPackage), @YearPriceExPackage = sum(ActualPriceExPackage), @YearPriceExPackageExVat = sum(ActualPriceExPackageExVat), @YearPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
from mistableView WITH (nolock) 
where datestamp like @year + '%' and datestamp <= @rundate and valuetype = 1 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)

-----------------------------------------------------------------------------------
-------------------  YEAR PART END  -----------------------------------------------
-----------------------------------------------------------------------------------

--------------------------------------------------------------------------------------
-------------------  Remaining YEAR PART START  -------------------------------------
--------------------------------------------------------------------------------------
--------------------  Remaining YEAR INHOUSE - START  --------------------------------------
declare @RemainingYearnumberofrooms  int
declare @RemainingYearnumberofroomsBooked INT
DECLARE @RemainingYearPax INT
DECLARE @RemainingYearAdults INT
DECLARE @RemainingYearChildren1 INT
DECLARE @RemainingYearChildren2 INT
DECLARE @RemainingYearPaxBooked INT
DECLARE @RemainingYearAdultsBooked INT
DECLARE @RemainingYearChildren1Booked INT
DECLARE @RemainingYearChildren2Booked INT

select @RemainingYearnumberofrooms = sum(ActualNumberOfRooms), 
  @RemainingYearPax = sum(actualadult+actualchildren1+actualchildren2),
  @RemainingYearAdults = sum(actualadult),
  @RemainingYearChildren1 = sum(actualChildren1),
  @RemainingYearChildren2 = sum(actualChildren2),
  @RemainingYearPrice = sum(ActualPriceInclPackage), 
  @RemainingYearPriceExPackage = sum(ActualPriceExPackage), 
  @RemainingYearPriceExPackageExVat = sum(ActualPriceExPackageExVat), 
  @RemainingYearPriceInclPackageExVat = sum(ActualPriceInclPackageExVat)
from mistableView WITH (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 (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)
 
IF @rundate < @SystemDate
BEGIN
  select @RemainingYearnumberofroomsBooked = sum(BookedNumberOfRooms), 
    @RemainingYearPaxBooked = sum(Bookedadult+Bookedchildren1+Bookedchildren2),
    @RemainingYearAdultsBooked = sum(Bookedadult),
    @RemainingYearChildren1Booked = sum(BookedChildren1),
    @RemainingYearChildren2Booked = sum(BookedChildren2),
    @RemainingYearPriceBooked = sum(BookedPriceInclPackage), 
    @RemainingYearPriceExPackageBooked = sum(BookedPriceExPackage), 
    @RemainingYearPriceExPackageExVatBooked = sum(BookedPriceExPackageExVat), 
    @RemainingYearPriceInclPackageExVatBooked = sum(BookedPriceInclPackageExVat)
  from mistableView WITH (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 (@AReportOwnerReservation = 2 OR MisTableView.OwnerReservation = @AReportOwnerReservation)
  AND (@AInclNUL = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> -1)
  AND (@AInclEmpty = 1 OR ISNULL(MISTableView.ProductNumber, -666) <> 0)

  SET @RemainingYearnumberofrooms = ISNULL(@RemainingYearnumberofrooms, 0) + ISNULL(@RemainingYearnumberofroomsBooked, 0)
  SET @RemainingYearPax = ISNULL(@RemainingYearPax, 0) + ISNULL(@RemainingYearPaxBooked, 0)
  SET @RemainingYearAdults = ISNULL(@RemainingYearAdults, 0) + ISNULL(@RemainingYearAdultsBooked, 0)
  SET @RemainingYearChildren1 = ISNULL(@RemainingYearChildren1, 0) + ISNULL(@RemainingYearChildren1Booked, 0)
  SET @RemainingYearChildren2 = ISNULL(@RemainingYearChildren2, 0) + ISNULL(@RemainingYearChildren2Booked, 0)
  SET @RemainingYearPrice = ISNULL(@RemainingYearPrice, 0) + ISNULL(@RemainingYearPriceBooked, 0)
  SET @RemainingYearPriceExPackage = ISNULL(@RemainingYearPriceExPackage, 0) + ISNULL(@RemainingYearPriceExPackageBooked, 0)
  SET @RemainingYearPriceExPackageExVat = ISNULL(@RemainingYearPriceExPackageExVat, 0) + ISNULL(@RemainingYearPriceExPackageExVatBooked, 0)
  SET @RemainingYearPriceInclPackageExVat = ISNULL(@RemainingYearPriceInclPackageExVat, 0) + ISNULL(@RemainingYearPriceInclPackageExVatBooked, 0)
END

-------------------- Remaining Year INHOUSE - END  -----------------------------------------
-------------------- Remaining Year ARRIVALS - START  --------------------------------------
declare @RemainingYearnumberofroomsarrivals  int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 1, 1, 1, @RemainingYearnumberofroomsarrivals OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

-------------------- Remaining Year ARRIVALS - END  -----------------------------------------
-------------------- Remaining Year DAYROOM - START  ----------------------------------------
declare @RemainingYearnumberofdayrooms int
EXEC HotelManagerNumberOfRooms @rundate, @year, '', @HotelNumberStr, 4, 1, 1, @RemainingYearnumberofdayrooms OUTPUT, @AReportOwnerReservation, @AInclNUL, @AInclEmpty

-------------------- Remaining Year DAYROOM - END  ---------------------------------------
-------------------- Remaining Year OUT OF ORDER - START  ------------------------------
declare @RemainingYearoutoforder  int
SELECT @Yearoutoforder = ISNULL(SUM(NumberOfRooms * DATEDIFF(DAY, 
  CONVERT(DATETIME, CASE WHEN ArrivalDate<@Year + '0101' THEN @Year + '0101' ELSE ArrivalDate END, 112),
  CONVERT(DATETIME, CASE WHEN DepartureDate>@rundate THEN CONVERT(CHAR(8), CONVERT(DATETIME, @rundate, 112) + 1, 112) ELSE DepartureDate END, 112)
  )), 0)
FROM dbo.ReservationRoomline RL WITH (NOLOCK) 
INNER JOIN dbo.Rooms RM WITH (NOLOCK) ON RL.RoomNumber=RM.RoomNumber AND (@HotelNumberStr= '' OR RM.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr)))
INNER JOIN dbo.ReservationRoomlineXTab RLX WITH (NOLOCK) ON RLX.LineNumber=RL.LineNumber AND RLX.ReferenceNumber=0 AND ArrivalDate>@rundate AND DepartureDate>@Year + '0101'

-------------------- Remaining Year OUT OF ORDER - END  --------------------------------
-------------------- Remaining Year upgrade rooms - START  ---------------------------
declare @RemainingYearNumberOfRoomsUpgrade int
IF @HotelNumberStr <> ''
BEGIN
  SELECT DISTINCT @RemainingYearNumberOfRoomsUpgrade = count(*)
  FROM RESERVATIONROOMLINE WITH (NOLOCK) 
  INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 
  INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
  INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
  LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
  WHERE (1=1) 
  AND (((
  ((convert(varchar(8), RESERVATIONROOMLINE.DepartureDate, 112) like @year + '%') AND (RESERVATIONROOMLINE.ArrivalDate > @RunDate) AND (Reservations.GuestNumber = 0)) 
  OR  ((convert(varchar(8), RESERVATIONS.DepartureDate, 112) like @year + '%') AND (RESERVATIONS.ArrivalDate > @RunDate)) AND (Reservations.GuestNumber > 0))) 
  AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
   ) 
  AND ReservationMain.HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@HotelNumberStr))
  AND Reservations.Referencenumber > 0
  AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
END
ELSE
BEGIN
  SELECT DISTINCT @RemainingYearNumberOfRoomsUpgrade = count(*)
  FROM RESERVATIONROOMLINE WITH (NOLOCK) 
  INNER JOIN RESERVATIONROOMLINEXTAB WITH (NOLOCK) ON RESERVATIONROOMLINE.LINENUMBER = RESERVATIONROOMLINEXTAB.LINENUMBER 

  INNER JOIN RESERVATIONMAIN WITH (NOLOCK) 
  INNER JOIN RESERVATIONS WITH (NOLOCK) ON RESERVATIONMAIN.REFERENCENUMBER = RESERVATIONS.ReferenceNumber ON RESERVATIONROOMLINEXTAB.ReferenceNumber = RESERVATIONS.ReferenceNumber AND RESERVATIONROOMLINEXTAB.GuestNumber = RESERVATIONS.GuestNumber 
  LEFT OUTER JOIN RESERVATIONADDRESS WITH (NOLOCK) 
  INNER JOIN RESERVATIONADDRESSXTAB WITH (NOLOCK) ON RESERVATIONADDRESS.AddressNumber = RESERVATIONADDRESSXTAB.ADDRESSNUMBER  AND AddressType = 1 ON RESERVATIONS.ReferenceNumber = RESERVATIONADDRESSXTAB.REFERENCENUMBER AND RESERVATIONS.GuestNumber = RESERVATIONADDRESSXTAB.GUESTNUMBER 
  WHERE (1=1) 
  AND (((
  ((convert(varchar(8), RESERVATIONROOMLINE.DepartureDate, 112) like @year + '%') AND (RESERVATIONROOMLINE.ArrivalDate > @RunDate) AND (Reservations.GuestNumber = 0)) 
  OR  ((convert(varchar(8), RESERVATIONS.DepartureDate, 112) like @year + '%') AND (RESERVATIONS.ArrivalDate > @RunDate)) AND (Reservations.GuestNumber > 0))) 
  AND (((RESERVATIONROOMLINE.DepartureDate <> RESERVATIONROOMLINE.ArrivalDate) AND (Reservations.GuestNumber = 0)) OR ((RESERVATIONS.DepartureDate <> RESERVATIONS.ArrivalDate)) AND (Reservations.GuestNumber > 0))
   ) 
  AND Reservations.Referencenumber > 0
  AND (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = ReservationRoomLine.BookedTypeNumber) <> (SELECT Type FROM RoomTypes WITH (NOLOCK) WHERE RoomTypeNumber = (SELECT RoomTypeNumber FROM Rooms WITH (NOLOCK) WHERE Rooms.RoomNumber = ReservationRoomLine.RoomNumber))
END

-------------------- Remaining Year Upgrade rooms - END  -----------------------------------
------------------------------------------------------------------------------------
------------------- Remaining Year PART END  -----------------------------------------------
------------------------------------------------------------------------------------

-- 1 = Rum Inhouse
-- 2 = Tidl. afrejse
-- 3 = Belęg. Procent

-- 6 = Reservationer Ankomster
-- 7 = Reservationer WalkIN
-- 8 = Reservationer Blivere
-- 9 = Reservationer NoShows
-- 10 = DayRooms
-- 11 = Rooms Upgraded

create table #temp_res 
(
  row_id int,
  today money,
  thismonth money,
  thisyear money, 
  budgetmonth money,
  budgetyear money
)

IF @ooo <= 0 -- dont include OOO
BEGIN
  SET @todayoutoforder = 0
  SET @monthoutoforder = 0
  SET @Yearoutoforder = 0
  SET @Remainingmonthoutoforder = 0
  SET @RemainingYearoutoforder = 0
END

IF @inclVAT = 1 AND @inclPackage = 0
BEGIN
  SET @todayPrice = @TodayPriceExPackage
  SET @MonthPrice = @MonthPriceExPackage
  SET @RemainingMonthPrice = @RemainingMonthPriceExPackage
  SET @YearPrice = @YearPriceExPackage
  SET @RemainingYearPrice = @RemainingYearPriceExPackage
END
ELSE IF @inclVAT = 0 AND @inclPackage = 0
BEGIN
  SET @todayPrice = @TodayPriceExPackageExVat
  SET @MonthPrice = @MonthPriceExPackageExVat
  SET @RemainingMonthPrice = @RemainingMonthPriceExPackageExVat
  SET @YearPrice = @YearPriceExPackageExVat
  SET @RemainingYearPrice = @RemainingYearPriceExPackageExVat
END
ELSE IF @inclVAT = 0 AND @inclPackage = 1
BEGIN
  SET @todayPrice = @TodayPriceInclPackageExVat
  SET @MonthPrice = @MonthPriceInclPackageExVat
  SET @RemainingMonthPrice = @RemainingMonthPriceInclPackageExVat
  SET @YearPrice = @YearPriceInclPackageExVat
  SET @RemainingYearPrice = @RemainingYearPriceInclPackageExVat
END

SET @Remainingmonthnumberofdayrooms = isnull(@Remainingmonthnumberofdayrooms,1)
IF @Remainingmonthnumberofdayrooms = 0
  SET @Remainingmonthnumberofdayrooms = 1

SET @RemainingYearnumberofrooms = isnull(@RemainingYearnumberofrooms,1)
IF @RemainingYearnumberofrooms = 0
  SET @RemainingYearnumberofrooms = 1

DECLARE @TodayTurnoverperHour MONEY
IF ISNULL(@AWorkHour, 0) > 0
BEGIN
  SET @TodayTurnoverperHour = (ISNULL(@todayPrice, 0) + ISNULL(@AAdditionalTO, 0)) / @AWorkHour
END
ELSE
  SET @TodayTurnoverperHour = 0

-- 1 = Rum Inhouse
select @todaynumberofrooms as todaynumberofrooms, @monthnumberofrooms as monthnumberofrooms, @yearnumberofrooms as yearnumberofrooms, @Remainingmonthnumberofrooms as monthbudgetnumberofrooms, @RemainingYearnumberofrooms as yearbudgetnumberofrooms, 
-- 2 = Tidl. afrejse
@TodayEarlyDeparture as todayearly, @MonthEarlyDeparture as monthearly, @YearEarlyDeparture as yearearly, 0 as monthbudgetearly, 0 as yearbudgetearly,

-- 3 = Belęg. Procent
((convert(money, isnull(@todaynumberofrooms,0)) * 100) / convert(money, isnull(@totalroom, 0)-isnull(@todayoutoforder, 0))) as toDayOccPercent, 
((convert(money, isnull(@monthnumberofrooms,0)) * 100) / (convert(money, isnull(@totalroom, 0)*@DayOfMonth)-isnull(@monthoutoforder, 0))) as MonthOccPercent, 
((convert(money, isnull(@yearnumberofrooms,0)) * 100) / (convert(money, isnull(@totalroom, 0)*@DayOfYear)-isnull(@Yearoutoforder, 0))) as YearOccPercent, 
((convert(money, isnull(@Remainingmonthnumberofrooms,0)) * 100) / (convert(money, isnull(@totalroom, 0)*@Remainingdayofmonth)-isnull(@Remainingmonthoutoforder, 0))) as RemainingMonthOccPercent, 
((convert(money, isnull(@Remainingyearnumberofrooms,0)) * 100) / (convert(money, isnull(@totalroom, 0)*@RemainingDayOfYear)-isnull(@RemainingYearoutoforder, 0))) as RemainingYearOccPercent, 

-- 4 = Pax
@RemainingmonthPax as monthbudgetpax, 
@RemainingmonthAdults as monthbudgetAdults, 
@RemainingmonthChildren1 as monthbudgetChildren1, 
@RemainingmonthChildren2 as monthbudgetChildren2, 
@RemainingYearPax as yearbudgetpax,
@RemainingYearAdults as yearbudgetAdults,
@RemainingYearChildren1 as yearbudgetChildren1,
@RemainingYearChildren2 as yearbudgetChildren2,

-- 5 = Pax / Rum
convert(money, isnull(@RemainingmonthPax, 1)) / (convert(money, (isnull(@Remainingmonthnumberofrooms, 0) + ISNULL(@Remainingmonthnumberofdayrooms, 0)))) as monthbudgetpaxperroom, 
convert(money, isnull(@RemainingmonthAdults, 1)) / (convert(money, (isnull(@Remainingmonthnumberofrooms, 0) + ISNULL(@Remainingmonthnumberofdayrooms, 0)))) as monthbudgetAdultsperroom, 
convert(money, isnull(@RemainingmonthChildren1, 1)) / (convert(money, (isnull(@Remainingmonthnumberofrooms, 0) + ISNULL(@Remainingmonthnumberofdayrooms, 0)))) as monthbudgetChildren1perroom, 
convert(money, isnull(@RemainingmonthChildren2, 1)) / (convert(money, (isnull(@Remainingmonthnumberofrooms, 0) + ISNULL(@Remainingmonthnumberofdayrooms, 0)))) as monthbudgetChildren2perroom, 
convert(money, isnull(@RemainingYearPax, 1)) / (convert(money, (isnull(@RemainingYearnumberofrooms, 0) + ISNULL(@RemainingYearnumberofdayrooms, 0)))) as yearbudgetpaxperroom, 
convert(money, isnull(@RemainingYearAdults, 1)) / (convert(money, (isnull(@RemainingYearnumberofrooms, 0) + ISNULL(@RemainingYearnumberofdayrooms, 0)))) as yearbudgetAdultsperroom, 
convert(money, isnull(@RemainingYearChildren1, 1)) / (convert(money, (isnull(@RemainingYearnumberofrooms, 0) + ISNULL(@RemainingYearnumberofdayrooms, 0)))) as yearbudgetChildren1perroom, 
convert(money, isnull(@RemainingYearChildren2, 1)) / (convert(money, (isnull(@RemainingYearnumberofrooms, 0) + ISNULL(@RemainingYearnumberofdayrooms, 0)))) as yearbudgetChildren2perroom, 

-- 6 = Reservationer Ankomster
@todaynumberofroomsarrivals as todaynumberofroomsarrivals, @monthnumberofroomsarrivals as monthnumberofroomsarrivals, @yearnumberofroomsarrivals as yearnumberofroomsarrivals, @Remainingmonthnumberofroomsarrivals as monthbudgetnumberofroomsarrivals, @RemainingYearnumberofroomsarrivals as yearbudgetnumberofroomsarrivals, 

-- 7 = Reservationer WalkIN
@todaynumberofromswalkin as todaynumberofromswalkin, @monthnumberofromswalkin as monthnumberofromswalkin, @yearnumberofromswalkin as yearnumberofromswalkin, 0 as monthbudgetnumberofromswalkin, 0 as yearbudgetnumberofromswalkin, 

-- 8 = Reservationer Blivere
@todaynumberofrooms-@todaynumberofromswalkin-@todaynumberofroomsarrivals-@todaynoshow as todaystays,
@monthnumberofrooms-@monthnumberofromswalkin-@monthnumberofroomsarrivals-@monthnoshow as monthstays,
@yearnumberofrooms-@yearnumberofromswalkin-@yearnumberofroomsarrivals-@yearnoshow as yearstays,
@Remainingmonthnumberofrooms-@Remainingmonthnumberofroomsarrivals as monthBudgetstays,
@RemainingYearnumberofrooms-@RemainingYearnumberofroomsarrivals as YearBudgetstays,

-- 9 = Reservationer NoShows
@todaynoshow as todaynoshow, @monthnoshow as monthnoshow, @yearnoshow as yearnoshow, 0 as monthbudgetnoshow, 0 as yearbudgetnoshow,

-- 10 = DayRooms
@todaynumberofdayrooms as todaynumberofdayrooms, @monthnumberofdayrooms as monthnumberofdayrooms, @yearnumberofdayrooms as yearnumberofdayrooms,
@RemainingMonthNumberOfDayrooms as monthBudgetnumberofdayrooms, @RemainingYearNumberOfDayrooms as YearBudgetnumberofdayrooms,

-- 11 = Rooms Upgraded
@TodayNumberOfRoomsUpgrade as TodayNumberOfRoomsUpgrade, @MonthNumberOfRoomsUpgrade as MonthNumberOfRoomsUpgrade, @YearNumberOfRoomsUpgrade as YearNumberOfRoomsUpgrade,
-- 12 = revPar  
@todayPrice as TodayRevPar, @monthPrice AS MonthRevPar, @yearPrice AS YearRevPar, 
@RemainingmonthPrice AS MonthBudgetRevPar, @RemainingYearPrice AS YearBudgetRevPar,
-- 13 = OOO
@todayoutoforder AS TodayOutOfOrder, @monthoutoforder AS MonthOutOfOrder, @Yearoutoforder AS YearOutOfOrder, 
@Remainingmonthoutoforder AS RemainingMonthOutOfOrder, @RemainingYearoutoforder AS RemainingYearOutOfOrder,

-- 14 = TurnoverperHour
@TodayTurnoverperHour AS TodayTurnoverperHour

DROP TABLE #temp_res