[SQL Server]整理出日期區間資料中最長持續時間起迄

       最近遇到需要在不同類別各時間區段中，統計出每個類別最長不中斷的月數是多少。

資料大略如下圖：

因為程式老舊的關係所以只能在Stored Procedure上擴充，
雖然SQL也是可以寫迴圈來處理資料，但我盡量會避免在SQL上寫這種類似後端程式的處理。
因此花了點時間想該怎麼只靠SQL去整理出我要的資料。
最後終於寫出下面幾個SQL步驟來達到結果：

--1.為了使資料好處理，我首先就是從重疊區段中篩出關鍵點(起點、終點)資料
--找出日期接續起點和終點
SELECT RowNum =ROW_NUMBER() OVER (PARTITION BY CLASS ORDER BY DatePoint), *
INTO #TEMP
FROM
(
    --接續起點
    SELECT DISTINCT CLASS, DatePoint=STDT, PointType='SP' FROM TableA A
    --條件1.日期起點不能在任何其他日期區段內，但起點相同時忽略(因為有可能發生長區間包短區間的狀況，ex:ID:1、2)
    --因為是看日期接續，所以範圍會+1-1天來當條件
    WHERE NOT EXISTS (SELECT TOP 1 1 FROM TableA WHERE ID<>A.ID AND CLASS=A.CLASS AND STDT<>A.STDT AND A.STDT BETWEEN STDT AND DATEADD(d,1, CONVERT(DATETIME, ENDT)))
    --條件2.日期終點必須存在其他日期區段內
    AND EXISTS (SELECT TOP 1 1 FROM TableA WHERE ID<>A.ID AND CLASS=A.CLASS AND A.ENDT BETWEEN DATEADD(d,-1, CONVERT(DATETIME, STDT)) AND ENDT)

    UNION ALL

    --接續終點(條件和起點相反)
    SELECT DISTINCT CLASS, DatePoint=ENDT, PointType='EP' FROM TableA A
    WHERE EXISTS (SELECT TOP 1 1 FROM TableA WHERE ID<>A.ID AND CLASS=A.CLASS AND A.STDT BETWEEN STDT AND DATEADD(d,1, CONVERT(DATETIME, ENDT)))
    AND NOT EXISTS (SELECT TOP 1 1 FROM TableA WHERE ID<>A.ID AND CLASS=A.CLASS AND ENDT<>A.ENDT AND A.ENDT BETWEEN DATEADD(d,-1, CONVERT(DATETIME, STDT)) AND ENDT)
) A

上面SQL執行結果如下圖：

--2.將點連成線
--SP、EP連點計算
SELECT A.CLASS, ST=A.DatePoint, ED=B.DatePoint
, MouthCount= DATEDIFF(M, A.DatePoint, B.DatePoint)
--起終點必然成雙成對出現
FROM (SELECT * FROM #TEMP WHERE PointType='SP') A
LEFT JOIN (SELECT * FROM #TEMP WHERE PointType='EP') B ON A.CLASS=B.CLASS AND B.RowNum=A.RowNum+1

UNION ALL
--沒重疊區段也納入比較
SELECT A.CLASS, A.STDT, A.ENDT
, MouthCount= DATEDIFF(M, A.STDT, A.ENDT)
FROM TableA A
--起點不在任何區段內
WHERE NOT EXISTS (SELECT TOP 1 1 FROM TableA WHERE ID<>A.ID AND CLASS=A.CLASS AND A.STDT BETWEEN STDT AND DATEADD(d,1, CONVERT(DATETIME, ENDT)))
--終點也不在任何區段內
AND NOT EXISTS (SELECT TOP 1 1 FROM TableA WHERE ID<>A.ID AND CLASS=A.CLASS AND A.ENDT BETWEEN DATEADD(d,-1, CONVERT(DATETIME, STDT)) AND ENDT)

結果如下：

最後只要再透過MAX、GROUP BY處理，就可得到每個類別的最長不中斷月數。

結語：雖然之前有處理過類似的日期區間重疊問題(可參考這篇文章)，但透過純SQL來處理倒是沒有，也算是不錯的經驗。