Qasim0787 Qasim0787 - 7 months ago 10
SQL Question

How to get missing dates from a sequence

I have asked one question previously..

Finding missing dates in a sequence

I what I want to find out very first missing date for each ID.

ID DATE
123 7/1/2015
123 6/1/2015
123 5/1/2015
123 4/1/2015
123 9/1/2014
123 8/1/2014
123 7/1/2014
123 6/1/2014
456 11/1/2014
456 10/1/2014
456 9/1/2014
456 8/1/2014
456 5/1/2014
456 4/1/2014
456 3/1/2014
789 9/1/2014
789 8/1/2014
789 7/1/2014
789 6/1/2014
789 5/1/2014
789 4/1/2014
789 3/1/2014


Expected results:

ID DATE
123 10/1/2014
456 6/1/2014

Answer

Try something like this:

EDITED

SELECT ID, ADD_MONTHS(MIN(DATE),1) 
FROM (seelct ID, DATE from yourTable a where
      not exists (select ID from yourTable b
                   where a.ID = b.ID and ADD_MONTHS(a.DATE,1) = b.DATE) )
GROUP BY ID
HAVING COUNT(*) > 1 -- every ID has one last month
Comments