Find A Date Range Gap In A Daily Loaded Table

:SETVAR DatabaseName "NAME"
:SETVAR SourceTable "dbo.TABLE"
:SETVAR ControlColumn "etl_date"
USE [$(DatabaseName)]

DECLARE @Tbl1 TABLE (ID INT IDENTITY(1,1) , Dater1 DateTime)DECLARE @Tbl2 TABLE (ID INT IDENTITY(1,1) , Dater2 DateTime)DECLARE @DateFilter DATETIME = '2013-01-05 00:00:00.000'
INSERT INTO @Tbl1SELECT DISTINCT eff_start_date FROM $(SourceTable)WHERE $(ControlColumn) >= @DateFilterORDER BY eff_start_date
INSERT INTO @Tbl2SELECT DISTINCT eff_start_date FROM $(SourceTable)WHERE $(ControlColumn) >= @DateFilterORDER BY eff_start_date
SELECT '$(SourceTable)' SourceTable,t.Dater2,CASE
WHEN (CAST(o.Dater1 AS DATE) <> CAST(DATEADD(D,-1,t.Dater2) AS DATE)) THEN 'FAIL'
ELSE 'PASS' END AS 'Skipped Day',CASEWHEN (DATEPART(weekday,t.Dater2) = 2) THEN 'Monday'WHEN (DATEPART(weekday,t.Dater2) = 3) THEN 'Tuesday'
WHEN (DATEPART(weekday,t.Dater2) = 4) THEN 'Wednesday'
WHEN (DATEPART(weekday,t.Dater2) = 5) THEN 'Thurday'WHEN (DATEPART(weekday,t.Dater2) = 6) THEN 'Friday'WHEN (DATEPART(weekday,t.Dater2) = 7) THEN 'Saturday'WHEN (DATEPART(weekday,t.Dater2) = 1) THEN 'Sunday'ELSE 'Nomads!' END DayOfWeeker FROM @Tbl1 o JOIN @Tbl2 t ON o.ID = (t.ID-1)WHERE o.ID <> 1ORDER BY Dater1 DESC