CREATE PROCEDURE [dbo].[OTAGetRestrictionInfoForChannel] @AStartDate DATETIME, @AEndDate DATETIME, @AHotelNumber INT, @ARateLevelChannelId INT, @ARateLevelIdCSV VARCHAR(1000) AS 
/*
  Created by Kent Johansen, May 2020
  Changes:
  
*/

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, @ARateLevelChannelId, 1, 0, -1, 0, 0

SELECT COUNT(*) AS cnt, DateStamp, RateLevelChannel.RateLevelChannelId, RateLevelChannel.Name, 
  RateLevelRestriction.RestrictionType, RateLevelRestriction.Id AS RateLevelRestrictionId, RateLevelRestriction.Name AS RateLevelRestrictionName
INTO #TmpResult
FROM #tmpInventory
INNER JOIN RateLevelChannelRateLevelXTab WITH (NOLOCK) ON RateLevelChannelRateLevelXTab.FKRateLevelId = #tmpInventory.RateLevelId
  AND (@ARateLevelIdCSV = '' OR RateLevelChannelRateLevelXTab.FKRateLevelId IN (SELECT DISTINCT Number FROM iter_intlist_to_table(@ARateLevelIdCSV)))
INNER JOIN RateLevelChannel WITH (NOLOCK) ON RateLevelChannel.RateLevelChannelId = RateLevelChannelRateLevelXTab.FKRateLevelChannelId
INNER JOIN RateLevelRestriction WITH (NOLOCK) ON RateLevelRestriction.Id = #tmpInventory.RateLevelRestrictionId
WHERE RateLevelChannel.RateLevelChannelId = @ARateLevelChannelId
GROUP BY DateStamp, RateLevelChannel.RateLevelChannelId, RateLevelChannel.Name, RateLevelRestriction.RestrictionType, RateLevelRestriction.Id, RateLevelRestriction.Name

SELECT COUNT(*) AS cnt, DateStamp
INTO #cntPerDay
FROM #TmpResult
GROUP BY DateStamp

SELECT #TmpResult.DateStamp, RateLevelChannelId, Name,
(
  SELECT TOP 1 RestrictionType
  FROM #TmpResult tmp
  WHERE tmp.DateStamp = #TmpResult.DateStamp
) AS RestrictionType,
(
  SELECT TOP 1 RateLevelRestrictionId
  FROM #TmpResult tmp
  WHERE tmp.DateStamp = #TmpResult.DateStamp
) AS RateLevelRestrictionId,
(
  SELECT TOP 1 RateLevelRestrictionName
  FROM #TmpResult tmp
  WHERE tmp.DateStamp = #TmpResult.DateStamp
) AS RateLevelRestrictionName,
(
  SELECT cnt FROM #cntPerDay WHERE #cntPerDay.DateStamp = #TmpResult.DateStamp
) AS UniRestriction  
FROM #TmpResult
GROUP BY #TmpResult.DateStamp, RateLevelChannelId, Name

DROP TABLE #cntPerDay
DROP TABLE #tmpInventory
DROP TABLE #TmpResult