CREATE PROCEDURE dbo.MISMonthReportHistory @AHotelCustomerNumberCSV VARCHAR(1000), @AHotelNumberCSV VARCHAR(1000), @ASegmentNumberCSV VARCHAR(1000), @ARunDate DATETIME, @AFromDate DATETIME, @AToDate DATETIME, 
  @AInclVAT BIT, @AInclPackage BIT, @AMISValueType VARCHAR(50), @AInclCorrection BIT AS

/*
  Created by Kent Johansen, May 2020
  Changes:
    KJ 20210219: Optimized SQL
    KJ 20210120: Ext: ADR calculated as in MIS (turnover ex VAT and Packages and manually charged).

*/

SET NOCOUNT ON

DECLARE @StartDateStr CHAR(8)
DECLARE @EndDateStr CHAR(8)
DECLARE @NumberOfRooms INT

CREATE TABLE #tmpAvailability
(
  DateStamp CHAR(8) NOT NULL,
  RoomTypeNumber INT NOT NULL,
  SegmentNumber INT NOT NULL,
  PointOfSaleNumber INT NOT NULL,
  HotelNumber INT NOT NULL,
  SoldRoomType INT NOT NULL,
  ProductNumber INT NOT NULL,
  ReservationGroup INT NOT NULL,
  SoldRooms SMALLINT NULL,
  Adults SMALLINT NULL,
  Children1 SMALLINT NULL,
  Children2 SMALLINT NULL,
  NumberOfConfirmed SMALLINT NULL,
  NumberOfProvisionals SMALLINT NULL,
  NumberOfGuaranteed SMALLINT NULL,
  NumberOfTentative SMALLINT NULL,
  NumberOfWaitinglists SMALLINT NULL,
  NumberOfDayrooms SMALLINT NULL,
  FreeRoomsAllotment SMALLINT NULL,
  TotalRoomsAllotment SMALLINT NULL,
  PaxAllotment SMALLINT NULL,
  NumberOfRoomsSoldToday SMALLINT NULL,
  NumberOfOutOfOrder SMALLINT NULL,
  NumberOfOutForService SMALLINT NULL,
  NumberOfOccupied SMALLINT NULL,
  NumberOfOccupiedGroup SMALLINT NULL,
  SoldPrice MONEY NULL,
  RequestorID INT NULL,
  NumberOfRoomsSoldCourse smallint NULL,
  NumberOfRoomsSoldAttendee smallint NULL
)

SET @StartDateStr = CONVERT(CHAR(8), @AFromDate, 112)
SET @EndDateStr = CONVERT(CHAR(8), @AToDate, 112)

SELECT TOP 1 @ARunDate = SystemDate 
FROM MISHistory WITH (NOLOCK) 
WHERE SystemDate <= CAST(@ARunDate AS DATE)
ORDER BY SystemDate DESC

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

SELECT DISTINCT ReferenceNumber
INTO #tmpRef
FROM MISTableView WITH (NOLOCK)
WHERE DateStamp BETWEEN @AFromDate AND @AToDate
AND StaffID IN (SELECT UserIdNumber FROM #Users WITH (NOLOCK))
AND ValueType = 1

SELECT
DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS YearIdx, MISHistory.FK_SegmentNumber,
SUM(NumberOfRooms) AS SoldRoomsADR
INTO #MISTmpADRRoomHist
FROM MISHistory WITH (NOLOCK)
WHERE MISHistory.DateStamp BETWEEN @AFromDate AND @AToDate
AND MISHistory.SystemDate = @ARunDate
AND MISHistory.FK_HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISHistory.FK_ReferenceNumber IN (SELECT ReferenceNumber FROM #tmpRef)
AND MISHistory.FK_ReferenceNumber > 0
AND ValueType = 1
AND (@ASegmentNumberCSV = '' OR MISHistory.FK_SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)),
  MISHistory.FK_SegmentNumber

SELECT 
DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS YearIdx,
MISHistory.FK_SegmentNumber,
CASE WHEN @AInclPackage = 1 THEN
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISHistory.Price)
  ELSE  
    SUM(MISHistory.Price) - SUM(VATOfPrice)
  END
ELSE
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISHistory.Price) - SUM(Package)
  ELSE  
    SUM(MISHistory.Price) - SUM(VATOfPrice) - SUM(Package) - SUM(VATOfPackage)
  END
END AS TurnoverADR
INTO #MISTmpADRTurnoverHist
FROM MISHistory WITH (NOLOCK) 
WHERE MISHistory.DateStamp BETWEEN @AFromDate AND @AToDate
AND MISHistory.SystemDate = @ARunDate
AND MISHistory.FK_HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISHistory.ValueType = 1
AND MISHistory.FK_ReferenceNumber > 0
AND (@ASegmentNumberCSV = '' OR MISHistory.FK_SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND MISHistory.FK_StaffID IN (SELECT UserIdNumber FROM #Users) 
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)),
  MISHistory.ValueType, MISHistory.FK_SegmentNumber

--select * from #MISTmpADRRoomHist order by yearidx, MonthIdx, SegmentNumber
--select * from #MISTmpADRTurnoverHist order by yearidx, MonthIdx, SegmentNumber

SELECT
DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS YearIdx, MISTableView.SegmentNumber,
SUM(ActualNumberOfRooms) + SUM(BookedNumberOfRooms) AS SoldRoomsADR
INTO #MISTmpADRRoom
FROM MISTableView WITH (NOLOCK)
WHERE MISTableView.DateStamp >= @AFromDate 
AND MISTableView.DateStamp < @ARunDate
AND MISTableView.DateStamp <= @AToDate
AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISTableView.ReferenceNumber IN (SELECT ReferenceNumber FROM #tmpRef)
AND MISTableView.ReferenceNumber > 0
AND ValueType = 1
AND ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)
AND (@ASegmentNumberCSV = '' OR MISTableView.SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.SegmentNumber

SELECT 
DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS YearIdx,
MISTableView.SegmentNumber,
CASE WHEN @AInclPackage = 1 THEN
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISTableView.BookedPriceInclPackage) + SUM(MISTableView.ActualPriceInclPackage) 
  ELSE  
    SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat) 
  END
ELSE
  CASE WHEN @AInclVAT = 1 THEN
    SUM(MISTableView.BookedPriceExPackage) + SUM(MISTableView.ActualPriceExPackage) 
  ELSE  
    SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) 
  END
END AS TurnoverADR
INTO #MISTmpADRTurnover
FROM MISTableView WITH (NOLOCK) 
WHERE MISTableView.DateStamp >= @AFromDate 
AND MISTableView.DateStamp < @ARunDate
AND MISTableView.DateStamp <= @AToDate
AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISTableView.ValueType = 1
AND MISTableView.ReferenceNumber > 0
AND ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)
AND (@ASegmentNumberCSV = '' OR MISTableView.SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
AND MISTableView.StaffID IN (SELECT UserIdNumber FROM #Users) 
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.ValueType, MISTableView.SegmentNumber

--select * from #MISTmpADRRoom order by yearidx, MonthIdx, SegmentNumber
--select * from #MISTmpADRTurnover order by yearidx, MonthIdx, SegmentNumber

INSERT INTO #tmpAvailability 
  EXEC GenerateAvailability @StartDateStr, @EndDateStr, 1, @AHotelNumberCSV, '', 1, 0, '', @AIncludeOccupiedInOFS = 1
--SELECT * FROM #tmpAvailability

SELECT
CASE WHEN @AInclCorrection = 0 THEN
  MIN(#MISTmpADRTurnoverHist.TurnoverADR) 
ELSE
  CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISHistory.Price)
    ELSE  
      SUM(MISHistory.Price) - SUM(VATOfPrice)
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISHistory.Price) - SUM(Package)
    ELSE  
      SUM(MISHistory.Price) - SUM(VATOfPrice) - SUM(Package) - SUM(VATOfPackage)
    END
  END
END AS Turnover,
SUM(MISHistory.Adult) + SUM(MISHistory.Children1) + SUM(MISHistory.Children2) AS Pax,
CASE WHEN @AInclCorrection = 0 THEN
  MIN(#MISTmpADRRoomHist.SoldRoomsADR)
ELSE
  SUM(MISHistory.NumberOfRooms)
END AS SoldRooms,
(
  SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
  FROM #tmpAvailability
  WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112))
) AS OOO,
DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)) AS YearIdx,
MISHistory.ValueType, MISHistory.FK_SegmentNumber AS SegmentNumber, Segments.Text AS SegmentName
INTO #MISTmp
FROM MISHistory WITH (NOLOCK)
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISHistory.FK_SegmentNumber
LEFT OUTER JOIN #MISTmpADRRoomHist WITH (NOLOCK) ON #MISTmpADRRoomHist.YearIdx = DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND #MISTmpADRRoomHist.MonthIdx = DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND #MISTmpADRRoomHist.FK_SegmentNumber = MISHistory.FK_SegmentNumber
LEFT OUTER JOIN #MISTmpADRTurnoverHist WITH (NOLOCK) ON #MISTmpADRTurnoverHist.YearIdx = DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND #MISTmpADRTurnoverHist.MonthIdx = DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112))
  AND #MISTmpADRTurnoverHist.FK_SegmentNumber = MISHistory.FK_SegmentNumber
WHERE MISHistory.DateStamp BETWEEN @AFromDate AND @AToDate
AND MISHistory.SystemDate = @ARunDate
AND MISHistory.FK_HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISHistory.ValueType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AMISValueType))
AND (@ASegmentNumberCSV = '' OR MISHistory.FK_SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISHistory.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISHistory.DateStamp, 112)),
  MISHistory.ValueType, MISHistory.FK_SegmentNumber, Segments.Text

UNION

SELECT
CASE WHEN @AInclCorrection = 0 THEN
  MIN(#MISTmpADRTurnover.TurnoverADR) 
ELSE
  CASE WHEN @AInclPackage = 1 THEN
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableView.BookedPriceInclPackage) + SUM(MISTableView.ActualPriceInclPackage) 
    ELSE  
      SUM(MISTableView.BookedPriceInclPackageExVat) + SUM(MISTableView.ActualPriceInclPackageExVat) 
    END
  ELSE
    CASE WHEN @AInclVAT = 1 THEN
      SUM(MISTableView.BookedPriceExPackage) + SUM(MISTableView.ActualPriceExPackage) 
    ELSE  
      SUM(MISTableView.BookedPriceExPackageExVat) + SUM(MISTableView.ActualPriceExPackageExVat) 
    END
  END
END AS Turnover,
SUM(MISTableView.BookedAdult) + SUM(MISTableView.ActualAdult) + SUM(MISTableView.BookedChildren1) + SUM(MISTableView.ActualChildren1) + SUM(MISTableView.BookedChildren2) + SUM(MISTableView.ActualChildren2) AS Pax,
CASE WHEN @AInclCorrection = 0 THEN
  MIN(#MISTmpADRRoom.SoldRoomsADR)
ELSE
  SUM(MISTableView.BookedNumberOfRooms) + SUM(MISTableView.ActualNumberOfRooms) 
END AS SoldRooms,
(
  SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
  FROM #tmpAvailability
  WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112))
) AS OOO,
DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS MonthIdx,
DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)) AS YearIdx,
MISTableView.ValueType, MISTableView.SegmentNumber, Segments.Text AS SegmentName
FROM MISTableView WITH (NOLOCK)
LEFT OUTER JOIN Segments WITH (NOLOCK) ON Segments.Number = MISTableView.SegmentNumber
LEFT OUTER JOIN #MISTmpADRRoom WITH (NOLOCK) ON #MISTmpADRRoom.YearIdx = DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND #MISTmpADRRoom.MonthIdx = DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND #MISTmpADRRoom.SegmentNumber = MISTableView.SegmentNumber
LEFT OUTER JOIN #MISTmpADRTurnover WITH (NOLOCK) ON #MISTmpADRTurnover.YearIdx = DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND #MISTmpADRTurnover.MonthIdx = DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112))
  AND #MISTmpADRTurnover.SegmentNumber = MISTableView.SegmentNumber
WHERE MISTableView.DateStamp >= @AFromDate 
AND MISTableView.DateStamp < @ARunDate
AND MISTableView.DateStamp <= @AToDate
AND MISTableView.HotelCustomerNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelCustomerNumberCSV))
AND MISTableView.ReferenceNumber > 0
AND MISTableView.ValueType IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AMISValueType))
AND ISNULL(MISTableView.WareType, 0) NOT IN (10,11,12,13,14,15,16,17,18)
AND (@ASegmentNumberCSV = '' OR MISTableView.SegmentNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ASegmentNumberCSV)))
GROUP BY DATEPART(MONTH, CONVERT(DATETIME, MISTableView.DateStamp, 112)), 
  DATEPART(YEAR, CONVERT(DATETIME, MISTableView.DateStamp, 112)),
  MISTableView.ValueType, MISTableView.SegmentNumber, Segments.Text

SELECT @NumberOfRooms = COUNT(*) 
FROM Rooms WITH (NOLOCK) 
WHERE HotelNumber IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@AHotelNumberCSV))

SELECT 1 AS Total, -1 AS SegmentNumber, '' AS SegmentName, MonthIdx, YearIdx,
SUM(Turnover) AS Turnover, SUM(Pax) AS Pax, SUM(SoldRooms) AS SoldRooms, 
ISNULL((
  SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
  FROM #tmpAvailability
  WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = MonthIdx
  AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = YearIdx
), 0) AS OOO,
SUM(Turnover) / (CASE WHEN ISNULL(SUM(Pax), 0) = 0 THEN 1 ELSE SUM(Pax) END) AS TurnoverPerPax, 
@NumberOfRooms AS NumberOfRooms, 
(
  ISNULL((@NumberOfRooms * dbo.DaysInMonth(YearIdx, MonthIdx)) -
  ISNULL((
    SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
    FROM #tmpAvailability
    WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = MonthIdx
    AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = YearIdx
  ), 0), 0)
) AS Capacity,
SUM(Turnover) /
  CASE WHEN 
    (
      (@NumberOfRooms * dbo.DaysInMonth(YearIdx, MonthIdx)) -
        ISNULL((
        SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
        FROM #tmpAvailability
        WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = MonthIdx
        AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = YearIdx
      ), 0)
    ) = 0  
  THEN 
    1 
  ELSE 
    (
      (@NumberOfRooms * dbo.DaysInMonth(YearIdx, MonthIdx)) -
        ISNULL((
        SELECT SUM(#tmpAvailability.NumberOfOutOfOrder)
        FROM #tmpAvailability
        WHERE DATEPART(MONTH, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = MonthIdx
        AND DATEPART(Year, CONVERT(DATETIME, #tmpAvailability.DateStamp, 112)) = YearIdx
      ), 0)
    ) 
  END 
AS RevPAR,
SUM(Turnover) / (CASE WHEN ISNULL(SUM(SoldRooms), 0) = 0 THEN 1 ELSE SUM(SoldRooms) END) AS ADR
FROM #MISTmp
GROUP BY MonthIdx, YearIdx

UNION

SELECT 0 AS Total, SegmentNumber, SegmentName, MonthIdx, YearIdx,
Turnover AS Turnover, 0, SoldRooms, 0,
0,
0,
0,
0,
0
FROM #MISTmp
ORDER BY Total, MonthIdx

DROP TABLE #MISTmp
DROP TABLE #tmpAvailability
DROP TABLE #Users
DROP TABLE #tmpRef
DROP TABLE #MISTmpADRRoomHist
DROP TABLE #MISTmpADRTurnoverHist
DROP TABLE #MISTmpADRRoom
DROP TABLE #MISTmpADRTurnover