Mr. Bhosale Mr. Bhosale - 8 days ago 5
SQL Question

Find missing date as compare to calendar

I am explain problem in short.

select distinct DATE from #Table where DATE >='2016-01-01'


Output :

Date
2016-11-23
2016-11-22
2016-11-21
2016-11-19
2016-11-18


Now i need to find out missing date a compare to our calender dates from year '2016'

i.e. Here date '2016-11-20' is missing.

I want list of missing dates.

Thanks for reading this. Have nice day.

Answer

You need to generate dates and you have to find missing ones. Below with recursive cte i have done it

  ;WITH CTE AS
    (
    SELECT CONVERT(DATE,'2016-01-01') AS DATE1
    UNION ALL
    SELECT DATEADD(DD,1,DATE1) FROM CTE WHERE DATE1<'2016-12-31'
    )
    SELECT DATE1 MISSING_ONE FROM CTE
    EXCEPT 
    SELECT * FROM #TABLE1
    option(maxrecursion 0)
Comments