
-- what does our data look like?
select top 100 * from Forecasting.Forecast f with(nolock)
order by 1 desc
go

select * from Forecasting.Forecast f with(nolock)
where f.EntityId = 588 and f.BusinessDay = '20180821' and f.Status = 0

select top 2000 * from Forecasting.MetricDetail md with(nolock)
where ForecastID in (3071082, 3071249)
order by 1 desc

-- idea:
-- build a temp table of all dates in the range
-- match the date with bit value of has forecast

declare @entityId int = 588
	, @startDate datetime = '20180501'
	, @endDate datetime = '20180901'

declare @forecastDates table (
	ForecastDate datetime,
	HasForecast bit
)

;with daysRange as (
	select dateadd(day, 0, @startDate) as theDate
	union all
	select dateadd(day, 1, theDate) from daysRange
	where theDate < @endDate
)
insert into @forecastDates (ForecastDate, HasForecast)
select theDate, 0
from daysRange
option (MAXRECURSION 400)

--select * from @forecastDates -- rem
update @forecastDates
set HasForecast = 
	case
		when exists (select 1 from Forecasting.Forecast f with(nolock)
						where f.EntityId = @entityId and f.BusinessDay = d.ForecastDate and f.Status = 0
						and exists ( select 1 from Forecasting.MetricDetail md with(nolock) where md.ForecastId = f.ForecastId )
			) then 1
		else 0
	end
from @forecastDates d

select ForecastDate from @forecastDates
where HasForecast = 0
go


-- TESTS:
-- pick a store and run the SP to see what dates have no data and make sure the config setting days includes these dates.

-- list stores to test with...
select EntityID, Entity from tbEntity with(nolock) where EntityTypeID = 4
order by 1
go

-- get missing forecast for store matching our config date range.
declare @entityId bigint = 588
	, @configEnum varchar(50) = '83501'
	, @daysBack int = 7
	, @startDate date
	, @endDate date

select @daysBack = convert(int, [Value]) from tbConfigurationSetting with(nolock)
where ConfigurationEnum = @configEnum

set @endDate = getdate()
set @startDate = dateadd(DAY, -@daysBack, @endDate)

exec [Forecasting].[qryHasForecastForEntityWithDateRange] @entityId, @startDate, @endDate
go



