Find out missing date

Question

I use SQL Server 2008R2, I need to findout the missing dates.

 ID College  Dept   JoiningDate
  1 SAEC     CSE    1/2/2008
  2 MSEC     EEE    4/2/2008
  3 GOV      ECE    8/2/2008
  4 JKP      CSE    9/2/2008
  5 VLM      CSE    10/2/2008
  6 PTR      CSE    13/2/2008

Output

JoiningDate
2/2/2008
3/2/2008
5/2/2008
6/2/2008
7/2/2008
11/2/2008
12/2/2008

you have to join from another table which have all dates . so you can get missing dates by checking null in join values . — rahularyansharma, Apr 09 '13 at 12:19
your db is not mentioned by you . Once i have asked similar question for sql server 2008 so you can check this if you are only looking for logic http://stackoverflow.com/questions/12890967/show-all-dates-data-between-two-dates-if-no-row-exists-for-particular-date-then — rahularyansharma, Apr 09 '13 at 12:20
there is also sql fiddle link for this task http://www.sqlfiddle.com/#!3/268dc/44 — rahularyansharma, Apr 09 '13 at 12:34

score 3 · Answer 1 · edited May 23 '17 at 12:22

3

I already provided you same type question link in comments, if you still have confusion you can check this as follows

;with d(date) as (
    select cast('2/1/2008' as datetime)
    union all
    select date+1
    from d
    where date < '2/13/2008'
)

select  d.date CDate
from d
left join CollegeDate t
on t.JoiningDate = d.date
WHERE ID is null 
order by d.date

SQL FIDDLE LINK

edited May 23 '17 at 12:22

Community

1
1

answered Apr 09 '13 at 12:45

rahularyansharma

11,156
18
79
135

score 0 · Answer 2 · answered Apr 09 '13 at 13:42

Try this script, it finds out missing dates without specifying constants -

DECLARE @CollegeDate TABLE 
(
  College NVARCHAR(10)
, Dept NVARCHAR(10)
, JoiningDate DATETIME
)

INSERT INTO @CollegeDate (College, Dept, JoiningDate)
VALUES 
('SAEC', 'CSE', '2/1/2008'),
('MSEC', 'EEE', '2/4/2008'),
('GOV', 'ECE', '2/8/2008'),
('JKP', 'CSE', '2/9/2008'),
('VLM', 'CSE', '2/10/2008'),
('PTR', 'CSE', '2/13/2008')

;WITH dateout AS 
(
SELECT 
   JoiningDate = MIN(JoiningDate)
 , MaxJoiningDate = MAX(JoiningDate)
FROM @CollegeDate

UNION ALL

SELECT 
   JoiningDate + 1
 , MaxJoiningDate
FROM dateout
WHERE JoiningDate < MaxJoiningDate
)

SELECT d.JoiningDate
FROM dateout d
WHERE d.JoiningDate NOT IN (SELECT c.JoiningDate FROM @CollegeDate c)
ORDER BY d.JoiningDate

Find out missing date

2 Answers2