Sql-Server
尋找交叉日期的差距
這是我過去一個月一直在嘗試解決的問題，但沒有取得任何進展。從本質上講，我正在使用一個 HR 系統，該系統為員工儲存兩種主要類型的職位：主要 (P) 和代理 (A)。職位類型的相關性不是很大，但了解數據的結構方式很重要。員工將始終處於主要職位，但可能會在該職位空缺或該職位的主要人員休假期間臨時擔任代理職位。
這在系統中的表示方式如下圖所示（作為範例而不是按比例）：
P (2018-07-28 - 9999-99-99) <-------------------------------------------------------------> A (2018-09-04 - 2018-09-05) <---> A (2018-11-12 - 2018-11-13) <---> A (2018-11-19 - 2018-11-20) <--->由於我試圖加入位置資訊的一組數據，我需要將其相交以看起來像這樣，其中代理位置始終優先於主要位置。
P 2018-07-28 - 2018-09-03 <----------> A 2018-09-04 - 2018-09-05 <---> P 2018-09-06 - 2018-11-11 <-----------------------> A 2018-11-12 - 2018-11-13 <---> P 2018-11-14 - 2018-11-18 <-----> A 2018-11-19 - 2018-11-20 <---> P 2018-11-21 - 9999-99-99 <----->下面是直接來自數據庫的數據的表示，以大致了解它的外觀：
Employee Position Start Date End Date Position Type 1 30 2016-12-10 2016-12-11 P 1 30 2016-12-12 2017-12-08 P 1 20 2017-01-31 2017-02-02 A 1 20 2017-03-29 2017-03-31 A 1 20 2017-04-19 2017-04-21 A 1 20 2017-06-22 2017-06-23 A 1 20 2017-06-29 2017-06-30 A 1 20 2017-10-03 2017-10-06 A 1 30 2017-12-09 2018-07-26 P 1 20 2018-02-12 2018-03-02 A 1 20 2018-07-19 2018-07-20 A 1 30 2018-07-27 2018-07-27 P 1 30 2018-07-28 9999-99-99 P 1 20 2018-09-04 2018-09-05 A 1 20 2018-11-12 2018-11-13 A 1 20 2018-11-19 2018-11-20 A如您所見，日期連續的同一位置甚至有多個條目。這些需要組合成一個連續的條目，直到它們被代理位置相交/覆蓋。在過去的一個月裡，我一直在網際網路上搜尋不同的資源，甚至還沒有找到一個可行的解決方案。我最接近的是來自SQL – Combining Overlapping Date Rows，我已經從 Oracle 轉換了它，但我沒有足夠的 T-SQL 知識將它一直帶到我需要的地方，下面的原始 Oracle 程式碼：
SELECT KEY, MIN(TD) MINTD, MAX(TD) MAXTD FROM (SELECT KEY, TD, SUM(C) OVER (PARTITION BY KEY ORDER BY TD) SC FROM (SELECT KEY, D, TD, TPD, DECODE(SIGN(LTD-TPD),1,1,0) C FROM (SELECT KEY, D, TRUNC(D,'MM') TD, ADD_MONTHS(TRUNC(D,'MM'),-1) LTD, LAG(D,1) OVER (PARTITION BY KEY ORDER BY D) PD, LAG(TRUNC(D,'MM'),1) OVER (PARTITION BY KEY ORDER BY D) TPD FROM T1))) GROUP BY KEY, SC ORDER BY KEY, SC;關於 SELECT 查詢對上述日期進行分組和相交的任何想法？
首先，我認為您提供的連結不適合您的問題，因為它是關於合併重疊日期，並且您需要生成（GAP）新日期。
我會將您的數據分為兩組，一組對應於 Primary(s)，一組屬於 Acting(s)。
SELECT t1.*, ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) P1 FROM tbl t1 WHERE t1.PosType = 'P' ORDER BY t1.Employee, t1.StartDate ; GO員工 | 職位 | 開始日期 | 結束日期 | 位置類型 | P1 -------: | -------: | :----------| :----------| :------ | :- 1 | 30 | 2016 年 10 月 12 日 | 2016 年 11 月 12 日 | 磷 | 1 1 | 30 | 2016 年 12 月 12 日 | 2017 年 8 月 12 日 | 磷 | 2 1 | 30 | 2017 年 9 月 12 日 | 2018 年 7 月 26 日 | 磷 | 3 1 | 30 | 27/07/2018 | 27/07/2018 | 磷 | 4 1 | 30 | 2018 年 7 月 28 日 | 9999 年 12 月 31 日 | 磷 | 5下一個代理行包含在第二個主要行中：
12/12/2016 | 08/12/2017SELECT t2.*, ROW_NUMBER() OVER (ORDER BY t2.StartDate) P2 FROM tbl t2 WHERE t2.PosType = 'A' AND t2.StartDate >= '20161212' AND t2.StartDate <= '20171208' ORDER BY t2.Employee, t2.StartDate ; GO員工 | 職位 | 開始日期 | 結束日期 | 位置類型 | P2 -------: | -------: | :----------| :----------| :------ | :-- 1 | 20 | 2017 年 1 月 31 日 | 2017 年 2 月 2 日 | 一個 | 1 1 | 20 | 2017 年 3 月 29 日 | 2017 年 3 月 31 日 | 一個 | 2 1 | 20 | 2017 年 4 月 19 日 | 21/04/2017 | 一個 | 3 1 | 20 | 2017 年 6 月 22 日 | 2017 年 6 月 23 日 | 一個 | 4 1 | 20 | 2017 年 6 月 29 日 | 2017 年 6 月 30 日 | 一個 | 5 1 | 20 | 2017 年 3 月 10 日 | 2017 年 6 月 10 日 | 一個 | 6現在讓我生成一個新數據集，對於每個 Primary，我將使用 OUTER JOIN 添加相應的 Acting，即使它們沒有包含 Acting，這個外部連接也會返回所有 Primary。
SELECT t1.*, COALESCE(t3.P2, 0) P2, P, t3.PrevED, t3.SD, t3.ED, NextSD, t3.PT INTO #mydata FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY Employee ORDER BY Employee, StartDate) as P1 FROM tbl WHERE PosType = 'P' ) t1 OUTER APPLY (SELECT ROW_NUMBER() OVER (ORDER BY t2.StartDate) as P2, LAG(t2.EndDate) OVER (ORDER BY t2.StartDate) as PrevED, LEAD(t2.StartDate) OVER (ORDER BY t2.StartDate) as NextSD, t2.Position as P, t2.StartDate as SD, t2.EndDate as ED, t2.PosType as PT FROM tbl t2 WHERE t2.Employee = t1.Employee AND t2.PosType = 'A' AND t2.StartDate >= t1.StartDate AND t2.StartDate <= t1.EndDate ) t3 WHERE t1.PosType = 'P' ORDER BY t1.Employee, t1.StartDate, t3.SD ; CREATE CLUSTERED INDEX PK_Mydata ON #mydata(Employee, P1, P2); SELECT * FROM #mydata ORDER BY Employee, P1, P2; GO注意：我使用了臨時表 plus 和索引，但您可以嘗試使用 CTE。您應該檢查這兩個選項的性能。
這是新的場景：
員工 | 職位 | 開始日期 | 結束日期 | 位置類型 | P1 | P2 | 磷 | 上一頁 | 標清 | 教育署 | 下一個SD | PT -------: | -------: | :----------| :----------| :------ | :- | :- | ---: | :----------| :----------| :----------| :----------| :--- 1 | 30 | 2016 年 10 月 12 日 | 2016 年 11 月 12 日 | 磷 | 1 | 0 | 空 | 空 | 空 | 空 | 空 | 空值 1 | 30 | 2016 年 12 月 12 日 | 2017 年 8 月 12 日 | 磷 | 2 | 1 | 20 | 空 | 2017 年 1 月 31 日 | 2017 年 2 月 2 日 | 2017 年 3 月 29 日 | 一種 1 | 30 | 2016 年 12 月 12 日 | 2017 年 8 月 12 日 | 磷 | 2 | 2 | 20 | 2017 年 2 月 2 日 | 2017 年 3 月 29 日 | 2017 年 3 月 31 日 | 2017 年 4 月 19 日 | 一種 1 | 30 | 2016 年 12 月 12 日 | 2017 年 8 月 12 日 | 磷 | 2 | 3 | 20 | 2017 年 3 月 31 日 | 2017 年 4 月 19 日 | 21/04/2017 | 2017 年 6 月 22 日 | 一種 1 | 30 | 2016 年 12 月 12 日 | 2017 年 8 月 12 日 | 磷 | 2 | 4 | 20 | 21/04/2017 | 2017 年 6 月 22 日 | 2017 年 6 月 23 日 | 2017 年 6 月 29 日 | 一種 1 | 30 | 2016 年 12 月 12 日 | 2017 年 8 月 12 日 | 磷 | 2 | 5 | 20 | 2017 年 6 月 23 日 | 2017 年 6 月 29 日 | 2017 年 6 月 30 日 | 2017 年 3 月 10 日 | 一種 1 | 30 | 2016 年 12 月 12 日 | 2017 年 8 月 12 日 | 磷 | 2 | 6 | 20 | 2017 年 6 月 30 日 | 2017 年 3 月 10 日 | 2017 年 6 月 10 日 | 空 | 一種 1 | 30 | 2017 年 9 月 12 日 | 2018 年 7 月 26 日 | 磷 | 3 | 1 | 20 | 空 | 2018 年 12 月 2 日 | 2018 年 2 月 3 日 | 2018 年 7 月 19 日 | 一種 1 | 30 | 2017 年 9 月 12 日 | 2018 年 7 月 26 日 | 磷 | 3 | 2 | 20 | 2018 年 2 月 3 日 | 2018 年 7 月 19 日 | 20/07/2018 | 空 | 一種 1 | 30 | 27/07/2018 | 27/07/2018 | 磷 | 4 | 0 | 空 | 空 | 空 | 空 | 空 | 空值 1 | 30 | 2018 年 7 月 28 日 | 9999 年 12 月 31 日 | 磷 | 5 | 1 | 20 | 空 | 2018 年 4 月 9 日 | 2018 年 5 月 9 日 | 2018 年 12 月 11 日 | 一種 1 | 30 | 2018 年 7 月 28 日 | 9999 年 12 月 31 日 | 磷 | 5 | 2 | 20 | 2018 年 5 月 9 日 | 2018 年 12 月 11 日 | 2018 年 13 月 11 日 | 2018 年 11 月 19 日 | 一種 1 | 30 | 2018 年 7 月 28 日 | 9999 年 12 月 31 日 | 磷 | 5 | 3 | 20 | 2018 年 13 月 11 日 | 2018 年 11 月 19 日 | 20/11/2018 | 空 | 一種我添加了兩個分區 P1、P2 並在它們上設置了索引，這些分區將幫助我根據 4 種類型的行在這個新場景中過濾資訊：
|----------------------------------| 類型 1 |--P--| 類型 2 |--A--| 類型 3 |--P--| 類型 2 |--A--| 類型 3 |--P--| 類型 4類型 1：
沒有任何包含的代理的主要（S）。
- P2 = 0
類型 2：
如果存在，請在代理之間加上第一個代理和主要開始日期之間的間隔。
- P2 <> 0
- 代理開始日期 - 1 > 主要開始日期/代理結束日期
類型 3：
所有代理preriods。
- P2 <> 0
類型 4：
如果存在，最後代理和主要結束日期之間的正確間隔。
- P2 <> 0
- NextSD 為 NULL（最後執行）
- 代理結束日期 < 主要結束日期
現在選擇：
SELECT * FROM ( -- Primary(s) without Acting(s) SELECT Employee, Position, StartDate, EndDate, PosType FROM #mydata WHERE Employee = 1 AND P2 = 0 -- -- plus all Acting(s) -- UNION SELECT Employee, P, SD, ED, PT FROM #mydata WHERE Employee = 1 AND P2 <> 0 -- -- plus all (left) Gaps -- UNION SELECT Employee, Position, IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)), DATEADD(day, -1, SD), PosType FROM #mydata WHERE Employee = 1 AND P2 <> 0 AND DATEDIFF(day, IIF(PrevED IS NULL, StartDate, DATEADD(day, 1, PrevED)), SD) > 0 UNION -- -- plus all (last right) Gap -- SELECT Employee, Position, DATEADD(day, 1, ED), EndDate, PosType FROM #mydata WHERE Employee = 1 AND P2 <> 0 AND NextSD IS NULL AND DATEDIFF(day, ED, EndDate) > 0 ) T ORDER BY 1, 3; GO這是最終結果：
員工 | 職位 | 開始日期 | 結束日期 | 位置類型 -------: | -------: | :----------| :----------| :------ 1 | 30 | 2016 年 10 月 12 日 | 2016 年 11 月 12 日 | 磷 1 | 30 | 2016 年 12 月 12 日 | 2017 年 1 月 30 日 | 磷 1 | 20 | 2017 年 1 月 31 日 | 2017 年 2 月 2 日 | 一種 1 | 30 | 2017 年 3 月 2 日 | 28/03/2017 | 磷 1 | 20 | 2017 年 3 月 29 日 | 2017 年 3 月 31 日 | 一種 1 | 30 | 2017 年 1 月 4 日 | 2017 年 4 月 18 日 | 磷 1 | 20 | 2017 年 4 月 19 日 | 21/04/2017 | 一種 1 | 30 | 22/04/2017 | 21/06/2017 | 磷 1 | 20 | 2017 年 6 月 22 日 | 2017 年 6 月 23 日 | 一種 1 | 30 | 2017 年 6 月 24 日 | 28/06/2017 | 磷 1 | 20 | 2017 年 6 月 29 日 | 2017 年 6 月 30 日 | 一種 1 | 30 | 2017 年 1 月 7 日 | 2017 年 2 月 10 日 | 磷 1 | 20 | 2017 年 3 月 10 日 | 2017 年 6 月 10 日 | 一種 1 | 30 | 2017 年 7 月 10 日 | 2017 年 8 月 12 日 | 磷 1 | 30 | 2017 年 9 月 12 日 | 2018 年 11 月 2 日 | 磷 1 | 20 | 2018 年 12 月 2 日 | 2018 年 2 月 3 日 | 一種 1 | 30 | 2018 年 3 月 3 日 | 2018 年 7 月 18 日 | 磷 1 | 20 | 2018 年 7 月 19 日 | 20/07/2018 | 一種 1 | 30 | 21/07/2018 | 2018 年 7 月 26 日 | 磷 1 | 30 | 27/07/2018 | 27/07/2018 | 磷 1 | 30 | 2018 年 7 月 28 日 | 2018 年 3 月 9 日 | 磷 1 | 20 | 2018 年 4 月 9 日 | 2018 年 5 月 9 日 | 一種 1 | 30 | 2018 年 6 月 9 日 | 2018 年 11 月 11 日 | 磷 1 | 20 | 2018 年 12 月 11 日 | 2018 年 13 月 11 日 | 一種 1 | 30 | 2018 年 11 月 14 日 | 2018 年 11 月 18 日 | 磷 1 | 20 | 2018 年 11 月 19 日 | 20/11/2018 | 一種 1 | 30 | 21/11/2018 | 9999 年 12 月 31 日 | 磷db<>在這裡擺弄