DECLARE
	@entityId bigint = 875
	, @serviceTypes int = 6
	, @startDate datetime = '20200416 06:30'
	, @endDate datetime = '20200418 06:00'
	, @processingTime tinyint = 48
	, @itemId bigint = 18

select @startDate, @endDate

DECLARE @IDList table (id VARCHAR(MAX))
insert into @IDList (id)
select f.ForecastId
FROM (
	SELECT ForecastId, ROW_NUMBER() OVER (PARTITION BY EntityId, BusinessDay ORDER BY GenerationDate DESC) RowNumber 
	FROM Forecasting.Forecast WITH(NOLOCK)
	WHERE EntityId = @entityId
		AND BusinessDay >= CONVERT(date, @startDate)
		AND BusinessDay <= CONVERT(date, @endDate)
	) f WHERE RowNumber = 1

SELECT i.InventoryItemId,
	CAST(CASE WHEN i.ServiceType IN (@serviceTypes) THEN 1 ELSE 0 END AS bit) AS Excluded,
    Sum(i.RawTransactionCount)/36 AS RawTransactionCount,
    Sum(i.SystemTransactionCount)/36 AS SystemTransactionCount,
    Sum(i.ManagerTransactionCount)/36 AS ManagerTransactionCount,
    Sum(i.LastYearTransactionCount)/36 AS LastYearTransactionCount
FROM Forecasting.InventoryItemMetricDetail i WITH(NOLOCK)
WHERE i.ForecastId IN (select id from @IDList)
AND i.IntervalStart >= @startDate
AND i.IntervalStart <= @endDate
AND i.InventoryItemId = @itemId
GROUP BY i.InventoryItemId, CASE WHEN i.ServiceType IN (@serviceTypes) THEN 1 ELSE 0 END


set @startDate = DATEADD(Hour, @processingTime, @startDate)
set @endDate = DATEADD(Hour, @processingTime, @endDate)

select @startDate, @endDate

DECLARE @IDList2 table (id VARCHAR(MAX))
insert into @IDList2 (id)
select f.ForecastId
FROM (
	SELECT ForecastId, ROW_NUMBER() OVER (PARTITION BY EntityId, BusinessDay ORDER BY GenerationDate DESC) RowNumber 
	FROM Forecasting.Forecast WITH(NOLOCK)
	WHERE EntityId = @entityId
		AND BusinessDay >= CONVERT(date, @startDate)
		AND BusinessDay <= CONVERT(date, @endDate)
	) f WHERE RowNumber = 1

SELECT i.InventoryItemId,
	CAST(CASE WHEN i.ServiceType IN (@serviceTypes) THEN 1 ELSE 0 END AS bit) AS Excluded,
    Sum(i.RawTransactionCount)/36 AS RawTransactionCount,
    Sum(i.SystemTransactionCount)/36 AS SystemTransactionCount,
    Sum(i.ManagerTransactionCount)/36 AS ManagerTransactionCount,
    Sum(i.LastYearTransactionCount)/36 AS LastYearTransactionCount
FROM Forecasting.InventoryItemMetricDetail i WITH(NOLOCK)
WHERE i.ForecastId IN (select id from @IDList2)
AND i.IntervalStart >= @startDate 
AND i.IntervalStart <= @endDate
AND i.InventoryItemId = @itemId
GROUP BY i.InventoryItemId, CASE WHEN i.ServiceType IN (@serviceTypes) THEN 1 ELSE 0 END
