Combine SQL records based on dates

Question

I have the following table:

startDate   endDate
----------------------
01-01-2014  01-07-2014
01-08-2014  01-14-2014
01-15-2014  01-21-2014

01-28-2014  02-03-2014
02-04-2014  02-10-2014

I want to bundle the dates together to minimize the amount of records. The output result should look like this:

startDate   endDate
----------------------
01-01-2014  01-21-2014
01-28-2014  02-10-2014

The dates can be linked together if the the startDate of one record is one day later than the endDate of another.

Can this be achieved without using cursors?

Answer 1

As mentioned in comments you need to Recursive CTE plus Window Function to aggregate the consecutive days

;WITH cte
     AS (SELECT StartDate,
                EndDate
         FROM   yourtable
         UNION ALL
         SELECT a.StartDate,
                b.EndDate
         FROM   cte a
                JOIN yourtable b
                  ON Dateadd(DAY, 1, a.EndDate) = b.StartDate),
     cte1
     AS (SELECT StartDate,
                EndDate,
                Row_number()
                  OVER(
                    partition BY EndDate
                    ORDER BY StartDate ASC) AS rn
         FROM   cte)
SELECT StartDate,
       Max(EndDate) AS EndDate
FROM   cte1 a
WHERE  a.rn = 1
GROUP  BY StartDate
ORDER  BY EndDate 

SQLFIDDLE DEMO