Detect consecutive dates ranges using SQL

Question

I want to fill the calendar object which requires start and end date information. I have one column which contains a sequence of dates. Some of the dates are consecutive (have one day difference) and some are not.

InfoDate    2013-12-04  consecutive date [StartDate] 2013-12-05  consecutive date 2013-12-06  consecutive date [EndDate]  2013-12-09                   [startDate] 2013-12-10                   [EndDate]  2014-01-01                   [startDate] 2014-01-02  2014-01-03                   [EndDate]  2014-01-06                   [startDate] 2014-01-07                   [EndDate]  2014-01-29                   [startDate] 2014-01-30  2014-01-31                   [EndDate]  2014-02-03                   [startDate] 2014-02-04                   [EndDate] 

I want to pick each consecutive dates range’s start and end date (the first one and the last one in the block).

StartDate     EndDate  2013-12-04    2013-12-06 2013-12-09    2013-12-10 2014-01-01    2014-01-03 2014-01-06    2014-01-07 2014-01-29    2014-01-31 2014-02-03    2014-02-04 

I want to solve the problem using SQL only.

Answer 1

No joins or recursive CTEs needed. The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group.

WITH t AS (   SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i   FROM @d   GROUP BY InfoDate ) SELECT MIN(d),MAX(d) FROM t GROUP BY DATEDIFF(day,i,d)