CODEWITHSUNDEEP
sqlsql-serverdatetime
Mr. Bhosale
Mr. Bhosale

Reputation: 3106

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.

Upvotes: 2

Views: 420

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270593

You need to generate the dates and then find the missing ones. A recursive CTE is one way to generate a handful of dates. Another way is to use master..spt_values as a list of numbers:

with n as (
      select row_number() over (order by (select null)) - 1 as n
      from master..spt_values
     ),
     d as (
      select dateadd(day, n.n, cast('2016-01-01' as date)) as dte
      from n
      where n <= 365
     )
select d.date
from d left join
     #table t
     on d.dte = t.date
where t.date is null;

If you are happy enough with ranges of missing dates, you don't need a list of dates at all:

select date, (datediff(day, date, next_date) - 1) as num_missing
from (select t.*, lead(t.date) over (order by t.date) as next_date
      from #table t
      where t.date >= '2016-01-01'
     ) t
where next_date <> dateadd(day, 1, date);

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

Using CTE and get all dates in CTE table then compare with your table.

CREATE TABLE #yourTable(_Values DATE)
INSERT INTO #yourTable(_Values)
SELECT '2016-11-23' UNION ALL 
SELECT '2016-11-22' UNION ALL  
SELECT '2016-11-21' UNION ALL
SELECT '2016-11-19' UNION ALL  
SELECT '2016-11-18'   


DECLARE @DATE DATE = '2016-11-01'
;WITH CTEYear (_Date) AS
(
  SELECT @DATE
  UNION ALL
  SELECT DATEADD(DAY,1,_Date)
  FROM CTEYear
  WHERE _Date < EOMONTH(@DATE,0)
)

SELECT * FROM CTEYear
WHERE NOT EXISTS(SELECT 1 FROM #yourTable WHERE _Date = _Values)
OPTION(maxrecursion 0)

Upvotes: 1

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

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)

Upvotes: 2

Related Questions