Wednesday, 25 November 2015

Find Missing date Query

Ø  Source table

  select Distinct  CONVERT (DATE, EntDt) as dt from  dbo.K_RPT_EMAIL_STATUS
  order by CONVERT (DATE, EntDt)  asc


Ø  Create Function

Create FUNCTION [dbo].[GetAllDaysInBetween](@FirstDay DATETIME, @LastDay DATETIME)
RETURNS @retDays TABLE
(
    DayInBetween DATETIME
)
AS
BEGIN
    DECLARE @currentDay DATETIME
    SELECT @currentDay = @FirstDay

    WHILE @currentDay <= @LastDay
    BEGIN

        INSERT @retDays (DayInBetween)
            SELECT @currentDay

        SELECT @currentDay = DATEADD(DAY, 1, @currentDay)
    END

    RETURN
END

Ø  Final Query

SELECT  DayInBetween AS missingDate
       FROM dbo.GetAllDaysInBetween('2014-11-25', '2015-11-24') AS AllDaysInBetween
  WHERE NOT EXISTS
      (select Distinct  CONVERT (DATE, EntDt) as dt from  dbo.K_RPT_EMAIL_STATUS
        WHERE CONVERT (DATE, EntDt)  = AllDaysInBetween.DayInBetween  )