[SQL Server]找出資料日期區間重複的範圍和次數

        遇到資料日期欄位屬於起訖型態，且需要將重複範圍和次數計算出來時可以用到

首先建立時間區段重複的兩個概念
1.當區段開始重複時，必定是為某區段的頭在某個區段的中間時，這邊標註為Ｓ

2.當區段結束重複時，必定是為某區段的尾在某個區段的中間時，這邊標註為Ｅ

如下圖　紅黃區段為不同的時間資料 Ｓ標記代表開始重複的記號(2/2號同時也是紅色的頭)；Ｅ標記代表結束重複的記號(2/3號同時也是黃色的尾)

而ＳＥ中間就是重複的範圍，多區段以此類推

假設目前有資料TableA，如下圖
ID為流水號，Med為區段的種類(不同種類的區段要分開統計)，DateS時間起，DateE時間訖

這邊藉由SQL來找出所有的ＳＥ點

--第一段找出所有符合Ｓ點條件的日期
Select Distinct Med, DateS As DatePoint, 'S' As TimeType From [TableA] A
Where  exists ( Select 1 From [TableA] B Where A.Med=B.Med And A.ID <> B.ID And A.DateS <= B.DateE And A.DateS >= B.DateS )

Union All
--第二段找出所有符合Ｅ點條件的日期
Select Distinct Med, DateE As DatePoint, 'E' As TimeType From [TableA] A
Where  exists ( Select 1 From [TableA] B Where A.Med=B.Med And A.ID <> B.ID And A.DateE <= B.DateE And A.DateE >= B.DateS )
--最後將所有資料照種類，日期，型態排列
Order By Med, DatePoint, TimeType Desc

最後資料會如下圖

到這邊基本上資料就整理結束了

剩下的就只剩解析得到的資料(迴圈+If判斷 即可處理)
解析方式：
Ｓ符號 代表開始重複 所以重複次數+1；Ｅ符號 代表結束重複 所以重複次數-1;從0開始
所以資料可以利用迴圈來解析成如下
以A種類為例
(第1筆資料和第2筆資料區間) 20200101-20200102 (第1筆狀態Ｓ)區段內重複次數: 0+1=1
(第2筆資料和第3筆資料區間) 20200102-20200103 (第2筆狀態Ｓ)區段內重複次數: 1+1=2
(第3筆資料和第4筆資料區間) 20200103-20200105 (第3筆狀態Ｅ)區段內重複次數: 2-1=1
(第4筆資料和第5筆資料區間) 20200105-20200105 (第4筆狀態Ｓ)區段內重複次數: 1+1=2
(第5筆資料和第6筆資料區間) 20200105-20200110 (第5筆狀態Ｅ)區段內重複次數: 2-1=1
(第6筆資料和第7筆資料區間) 20200110-20200115 (第6筆狀態Ｅ)區段內重複次數: 1-1=0
(第7筆資料和第8筆資料區間) 20200115-20200120 (第7筆狀態Ｓ)區段內重複次數: 0+1=1

到這大致上已經完成了，最後只剩下去除頭尾重複的時間點。
以此資料來看日期頭尾為實點(代表有包含此時間點)所以必須將資料往重複次數高的地方推進。
簡單來說就是如果此筆為Ｓ時，則上個區間的結束日期必須往前扣一天；
此筆為Ｅ時，則此個區間的開始日期必須往後延一天

上面結果則改寫為
(第1筆資料和第2筆資料區間) 20200101-20200101 (第1筆狀態Ｓ)區段內重複次數: 1
(第2筆資料和第3筆資料區間) 20200102-20200103 (第2筆狀態Ｓ)區段內重複次數: 2
(第3筆資料和第4筆資料區間) 20200104-20200104 (第3筆狀態Ｅ)區段內重複次數: 1
(第4筆資料和第5筆資料區間) 20200105-20200105 (第4筆狀態Ｓ)區段內重複次數: 2
(第5筆資料和第6筆資料區間) 20200106-20200110 (第5筆狀態Ｅ)區段內重複次數: 1
(第6筆資料和第7筆資料區間) 20200111-20200114 (第6筆狀態Ｅ)區段內重複次數: 0
(第7筆資料和第8筆資料區間) 20200115-20200120 (第7筆狀態Ｓ)區段內重複次數: 1