CREATE PROCEDURE dbo.Pickup2Report @AHotelNumberCSV VARCHAR(500), @ARoomTypeNumberCSV VARCHAR(1000), @APeriodStart DATETIME, @APeriodEnd DATETIME, @ASourceDate DATETIME, @ADestDate DATETIME, @AInclOccupied BIT, @AShowRoomOption INT AS
/*
  Created by Kent Johansen, august 2022
  @AShowRoomOption: TShowRoomOption = (sroRooms, sroBeds, sroBoth)

  Changes:
    KJ 20220912: Bug: Fix for "Nothing shown if showing data for only rooms".

*/
SET NOCOUNT ON

SELECT * INTO #HotelNumbers FROM DBO.iter_intlist_to_table_No_Dummy(@AHotelNumberCSV)
SELECT * INTO #RoomTypeNumbers FROM DBO.iter_intlist_to_table_No_Dummy(@ARoomTypeNumberCSV)

SELECT CONVERT(DATETIME, DateStamp, 112) AS DateStamp, SUM(SoldRooms) + (CASE WHEN @AInclOccupied = 1 THEN SUM(NumberOfOccupied) ELSE 0 END) AS SoldRooms, RoomTypes.ParentRoomTypeNumber,
CAST(CASE WHEN MAX(ISNULL(RoomTypes.ParentRoomTypeNumber, 0)) > 0 THEN 1 ELSE 0 END AS BIT) AS IsSubRoomType
INTO #source
FROM RoomBookingStatusHist WITH (NOLOCK)
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RoomBookingStatusHist.RoomTypeNumber 
WHERE SystemDate = @ASourceDate
AND (ISNULL(@ARoomTypeNumberCSV, '') = '' OR RoomTypes.RoomTypeNumber IN (SELECT DISTINCT Number FROM #RoomTypeNumbers)) 
AND (ISNULL(@AHotelNumberCSV, '') = '' OR RoomTypes.HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers)) 
AND DateStamp BETWEEN @APeriodStart AND @APeriodEnd
AND DateStamp >= @ADestDate
GROUP BY DateStamp, RoomTypes.ParentRoomTypeNumber

SELECT CONVERT(DATETIME, DateStamp, 112) AS DateStamp, SUM(SoldRooms) + (CASE WHEN @AInclOccupied = 1 THEN SUM(NumberOfOccupied) ELSE 0 END) AS SoldRooms, RoomTypes.ParentRoomTypeNumber,
CAST(CASE WHEN MAX(ISNULL(RoomTypes.ParentRoomTypeNumber, 0)) > 0 THEN 1 ELSE 0 END AS BIT) AS IsSubRoomType
INTO #dest
FROM RoomBookingStatusHist WITH (NOLOCK)
LEFT OUTER JOIN RoomTypes WITH (NOLOCK) ON RoomTypes.RoomTypeNumber = RoomBookingStatusHist.RoomTypeNumber 
WHERE SystemDate = @ADestDate
AND (ISNULL(@ARoomTypeNumberCSV, '') = '' OR RoomTypes.RoomTypeNumber IN (SELECT DISTINCT Number FROM #RoomTypeNumbers)) 
AND (ISNULL(@AHotelNumberCSV, '') = '' OR RoomTypes.HotelNumber IN (SELECT DISTINCT Number FROM #HotelNumbers)) 
AND DateStamp BETWEEN @APeriodStart AND @APeriodEnd
GROUP BY DateStamp, RoomTypes.ParentRoomTypeNumber

SELECT DateStamp, SUM(SoldRooms) AS SoldRooms
INTO #source1
FROM #source
WHERE ((@AShowRoomOption = 2) OR (#source.IsSubRoomType = @AShowRoomOption))
GROUP BY DateStamp

SELECT DateStamp, SUM(SoldRooms) AS SoldRooms
INTO #dest1
FROM #dest
WHERE ((@AShowRoomOption = 2) OR (#dest.IsSubRoomType = @AShowRoomOption))
GROUP BY DateStamp

SELECT CASE WHEN #Source1.DateStamp IS NULL THEN #dest1.DateStamp ELSE #Source1.DateStamp END AS DateStamp,
ISNULL(#dest1.SoldRooms, 0) - ISNULL(#source1.SoldRooms, 0) AS PickUpRooms,
CAST(
CASE WHEN ISNULL(#dest1.SoldRooms, 0) = 0 THEN
  0
ELSE
  ((ISNULL(#dest1.SoldRooms, 0) - ISNULL(#source1.SoldRooms, 0))/CAST(ISNULL(#dest1.SoldRooms, 0) AS FLOAT)) * 100 
END AS DECIMAL(10, 1)) AS PickupPercentage
FROM #source1
FULL JOIN #dest1 ON #dest1.DateStamp = #source1.DateStamp
ORDER BY DateStamp

DROP TABLE #source
DROP TABLE #dest
DROP TABLE #source1
DROP TABLE #dest1
DROP TABLE #HotelNumbers
DROP TABLE #RoomTypeNumbers