Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

Find Missing Date Ranges in SQL Server

5.00/5 (1 vote)
10 Feb 2012CPOL 18.6K  
Find missing date periods from a table.

I have a SQL table like this (table name: tblDatePeriod):


ID	FromDate	ToDate
1	01/02/2011	01/03/2011
2	05/03/2011	25/03/2011
3	01/07/2011	09/07/2011
4	10/07/2011	28/07/2011
5	05/08/2011	25/08/2011
6	26/08/2011	01/09/2011
7	10/11/2011	31/12/2011

I need to output missing date periods based on FromDate and ToDate, like this:


FromDate    ToDate
02/03/2011  04/03/2011
26/03/2011  31/06/2011
29/07/2011  04/08/2011
02/09/2011  09/11/2011

SQL
DECLARE @dateSequence TABLE (dt DATE NOT NULL PRIMARY KEY)    
DECLARE @tblSeasonDates TABLE (nID INT, dFromDate DATE, dToDate DATE)    
 
-- Get main table details into temp table   
INSERT INTO @tblSeasonDates     
SELECT ID, FromDate, ToDate FROM tblDatePeriod WITH(NOLOCK) WHERE YEAR(FromDate) = 2011   
    
IF EXISTS (SELECT nID FROM @tblSeasonDates)    -- Check records exists or not
 BEGIN    
  DECLARE @minDateFrom DATE, @maxDateTo DATE;    
      
  -- Get date period as per given year    
  SELECT @minDateFrom = MIN(dFromDate), @maxDateTo = MAX(dToDate)     
  FROM @tblSeasonDates;    
    
  -- load table dateSequence as per above selected period    
  WITH dates AS     
  (     
   SELECT @minDateFrom AS initDate     
   UNION ALL     
   SELECT DATEADD(dd, 1, initDate) FROM dates WHERE initDate < @maxDateTo    
  )     
  INSERT INTO @dateSequence    
  SELECT initDate FROM dates      
  OPTION (MAXRECURSION 0);     
      
  -- Get missed dates from availability from & to dates     
  WITH datesCTE (missingDates) AS      
  (    
   SELECT dt AS missingDates FROM @dateSequence t1     
   LEFT OUTER JOIN @tblSeasonDates t2 ON dt BETWEEN t2.dFromDate AND t2.dToDate    
   WHERE t2.nID IS NULL    
  ),     
  -- Get From date missed    
  tblFrom as (SELECT tbl1.missingDates FROM datesCTE tbl1     
     LEFT OUTER JOIN datesCTE tbl2 ON DATEDIFF(dd, tbl2.missingDates, tbl1.missingDates) = 1    
     WHERE tbl2.missingDates IS NULL),    
  -- Get To date missed    
  tblTo as (SELECT tbl1.missingDates FROM datesCTE tbl1     
     LEFT OUTER JOIN datesCTE tbl2 ON DATEDIFF(dd, tbl1.missingDates, tbl2.missingDates) = 1    
     WHERE tbl2.missingDates IS NULL)    
      
  -- Final Output for missed date ranges       
  SELECT CAST(tblFrom.missingDates AS VARCHAR) missingFrom,     
      CAST(MIN(tblTo.missingDates) AS VARCHAR) missingTo         
  FROM tblFrom     
  INNER JOIN tblTo ON DATEDIFF(dd, tblFrom.missingDates, tblTo.missingDates) >= 0    
  GROUP BY tblFrom.missingDates    
  ORDER BY tblFrom.missingDates;    
END

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)