按不間斷日期對行進行分組

我想以這樣的方式返回數據，如果程式碼具有連續的活動日期和結束日期，它應該將 Min 作為活動日期和 Max 結束日期，但如果程式碼已停止，它應該顯示兩個日期

例如表

+----+------+---------------+------------+
|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  |
+----+------+---------------+------------+

願望輸出

+----+------+---------------+------------+
|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  |
+----+------+---------------+------------+

在 SQL Server 2012 上，您可以使用andLAG子句來獲得您所追求的內容，而無需任何自聯接。用於確定分區前一行的值是否與目前行的值相差一天。執行總計用於將各組聯繫在一起，最後通過簡單的匯總獲得所需的結果。SUM``ORDER BY``LAG``[End Date]``[Start Date]

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;

結果：

╔══════╦═══════╦═════════════╦════════════╗
║ 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 ║
╚══════╩═══════╩═════════════╩════════════╝

引用自：https://dba.stackexchange.com/questions/193680