CREATE PROCEDURE dbo.OTAGetRestrictionInfo @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumber INT AS 
/*
  Created by Kent Johansen, February 2019
  Changes:
    KJ 20200518: Ext: Added RateLevelRestrictionId to OTAGetInventory
*/

CREATE TABLE #tmpInventory
(
  SortNumber INT,
  DateStamp DATETIME,
  Name VARCHAR(50),
  RateLevelId INT,
  Allocated INT,
  Type VARCHAR(10),
  RoomTypeNumber INT,
  ProdcutNumber INT,
  RestrictionType SMALLINT,
  RestrictionLength INT,
  RateLevelRestrictionId INT
)

INSERT INTO #tmpInventory
  EXEC OTAGetInventory @AStartDate, @AEndDate, @AHotelNumber, -1, 1, 0, -1, 0, 0

SELECT COUNT(*) AS cnt, DateStamp, RateLevelChannel.FK_OTARequestorId, CustomerOTA.HotelCode, RestrictionType
INTO #TmpResult
FROM #tmpInventory
INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = #tmpInventory.RateLevelId
INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
  AND ISNULL(RateLevelChannel.MultipleRestrictions, 0) = 0
INNER JOIN CustomerOTA WITH (NOLOCK) ON CustomerOTA.RequestorID = RateLevelChannel.FK_OTARequestorId
WHERE NOT RateLevelChannel.FK_OTARequestorId IS NULL
GROUP BY DateStamp, RateLevelChannel.FK_OTARequestorId, CustomerOTA.HotelCode, RestrictionType

SELECT DateStamp, FK_OTARequestorId, HotelCode, 

CASE WHEN 
(
  SELECT COUNT(*) 
  FROM #TmpResult tmp 
  WHERE tmp.DateStamp = #TmpResult.DateStamp
  AND tmp.FK_OTARequestorId = #TmpResult.FK_OTARequestorId
  AND tmp.HotelCode = #TmpResult.HotelCode
  AND tmp.RestrictionType <> 1
) > 0 THEN 0 ELSE 1 END AS DayClosed
FROM #TmpResult
GROUP BY DateStamp, FK_OTARequestorId, HotelCode

DROP TABLE #tmpInventory
DROP TABLE #TmpResult