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
DECLARE @dateSequence TABLE (dt DATE NOT NULL PRIMARY KEY)
DECLARE @tblSeasonDates TABLE (nID INT, dFromDate DATE, dToDate DATE)
INSERT INTO @tblSeasonDates
SELECT ID, FromDate, ToDate FROM tblDatePeriod WITH(NOLOCK) WHERE YEAR(FromDate) = 2011
IF EXISTS (SELECT nID FROM @tblSeasonDates)
BEGIN
DECLARE @minDateFrom DATE, @maxDateTo DATE;
SELECT @minDateFrom = MIN(dFromDate), @maxDateTo = MAX(dToDate)
FROM @tblSeasonDates;
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);
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
),
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),
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)
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