Click here to Skip to main content
15,887,945 members
Articles / Database Development / SQL Server / SQL Server 2008
Tip/Trick

Find Missing Date Ranges in SQL Server

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
10 Feb 2012CPOL 18.4K   3  
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)


Written By
Web Developer Mahindra Logisoft Business Solution Limited, Chenn
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
-- There are no messages in this forum --