Group rows by uninterrupted dates I want to return data in such way that the data which have if code has continuous active dates and end dates it should give Min as active date and Max end date, but if code was discontinued it should show two dates

E.g
Table

+----+------+---------------+------------+
|Code| Decri|   Active Date |End Date    | 
+----+------+---------------+------------+
|1000| ABC  | 1/1/2011      | 30/6/2011  |
|1000| ABC  | 1/7/2011      | 30/6/2012  |
|1000| ABC  | 1/7/2012      | 30/6/2013  |
|1001| ABC  | 1/7/2013      | 30/6/2014  |
|1001| ABC  |12/20/2015     |12/20/2017  |
|1003| ABC  | 1/1/2011      |30/6/2011   |
|1003| ABC  | 1/7/2012      |30/6/2013   |
|1003| ABC  | 1/7/2014      |30/6/2015   |
|1003| ABC  | 1/7/2015      |12/20/2017  |
+----+------+---------------+------------+


Desire Output

+----+------+---------------+------------+
|Code| Decri|   Active Date |End Date    | 
+----+------+---------------+------------+
|1000| ABC  | 1/1/2011      | 30/6/2013  |
|1001| ABC  | 1/7/2013      | 30/6/2014  |
|1001| ABC  |12/20/2015     |12/20/2017  |
|1003| ABC  | 1/1/2011      |30/6/2013   |
|1003| ABC  | 1/7/2014      |12/20/2017  |
+----+------+---------------+------------+
On SQL Server 2012, you can use LAG and SUM with an ORDER BY clause to get what you're after without any self-joins. LAG is used to figure out if the [End Date] value from the previous row for a partition is exactly one day away from the [Start Date] value of the current row. The running total is used to tie the groups together and you get your desired results with a simple aggregate at the end.

SELECT
  Code
, Decri
, MIN([Active Date]) [Active Date]
, MAX([End Date]) [End Date]
FROM
(
    SELECT
      Code
    , Decri
    , [Active Date]
    , [End Date]
    , SUM(start_new_group) OVER (PARTITION BY Code, Decri ORDER BY [Active Date], [End Date]) group_id
    FROM
    (
        SELECT 
          Code
        , Decri
        , [Active Date]
        , [End Date]
        , CASE WHEN DATEADD(DAY, -1, [Active Date]) = LAG([End Date]) OVER (PARTITION BY Code, Decri ORDER BY [Active Date], [End Date]) THEN 0 ELSE 1 END start_new_group
        FROM #x
    ) t
) t2
GROUP BY Code, Decri, group_id;


Results:

╔══════╦═══════╦═════════════╦════════════╗
║ Code ║ Decri ║ Active Date ║  End Date  ║
╠══════╬═══════╬═════════════╬════════════╣
║ 1000 ║ ABC   ║ 2011-01-01  ║ 2013-06-30 ║
║ 1001 ║ ABC   ║ 2013-07-01  ║ 2014-06-30 ║
║ 1001 ║ ABC   ║ 2015-12-20  ║ 2017-12-20 ║
║ 1003 ║ ABC   ║ 2011-01-01  ║ 2013-06-30 ║
║ 1003 ║ ABC   ║ 2014-07-01  ║ 2017-12-20 ║
╚══════╩═══════╩═════════════╩════════════╝