我已經在一個問題上作業了幾天，但我似乎找不到正確的修復方法。
案例
我們想在員工辭職超過1天的時候創建一個新的序列號。我們有當前就業記錄和之前就業記錄的delta，所以我們可以檢查序列。我們想計算每條就業記錄的最小值（Start）和最大值（End），這些記錄的間隔不超過1天。
資料
| 員工 | 合同單位開始結束 | Delta|
|---|---|---|
| John Doe | 1 | A單元 |
通過查詢，它應該回饋給：
| 員工 | 合同單位開始結束 | Delta序列 | 。
|---|---|---|
| John Doe | 1 | A單元 |
這是因為序列1在2017年12月31日結束，而新的序列在2018年2月開始，所以記錄之間已經有超過1天的間隔。下面的序列都是2，因為它是持續的。
查詢
我已經用lag()和lead()嘗試了一些東西，但我一直在用我所擁有的資料樣本把自己弄到一個角落。當我在完整的資料集上運行它時，它就不作業了。
SELECT
雇員。
Start,
End,
DeltaPrevious,
三角洲。
DeltaNext。
case
when DeltaPrevious IS NULL AND Delta = 1 then 1
when DeltaPrevious = 1 AND Delta> 1 then min（Contract） OVER (PARTITION BY EmployeeOrder BY Contract ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
when DeltaPrevious > 1 AND Delta = 1 then min(Contract) OVER（PARTITION BY員工ORDER BY合同ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
end as 序列
FROM
契約
ORDER BY 雇員。
員工，開始 ASC ASC
希望有人有一個好主意。
謝謝你，
uj5u.com熱心網友回復：
如果我從你第二個表中的Sequence的定義中理解正確的話，你對DeltaNext更感興趣，而不是對Delta(Previous)。這里有一個嘗試，包括創建一個有兩個以上雇員的輸入日期樣本的代碼：
CREATE TABLE #input_table (Employee VARCHAR(255), [合同] INT, Unit VARCHAR(6), [Start] DATE, [End] DATE)
INSERT INTO #input_table
VALUES[/span
('John Doe', 1, 'Unit A', '2014-01-01', '2017-12-31') 。
('John Doe', 2, '單位A', '2018-02-01', '2018-12-31')。
('John Doe', 3, '單元B', '2019-01-01', '2020-05-31')。
('John Doe', 4, 'Unit A', '2020-06-01', NULL) 。
('Alice', 1, 'Unit A', '2020-01-01', NULL) 。
('Bob', 1, '單元C', '2020-01-01', '2020-02-20')
首先我們創建deltas:
SELECT *
, DeltaPrev = DATEDIFF(DAY, LAG([End], 1, NULL) OVER(PARTITION BY Employee
ORDER BY [Start]), [Start] ) --不相關（？）。
, DeltaNext = DATEDIFF(DAY, [End], LEAD([Start], 1, NULL) OVER(PARTITION BY) keyword">BY員工 ORDER BY [Start] )
INTO #cte_delta --我將在最后創建一個CTE。
FROM #input_table
然后我們定義Sequence：
SELECT *
, [Sequence] = CASE WHEN DeltaNext > 1 THEN 1 ELSE 2 END
INTO #cte_sequence
FROM #cte_delta
然后我們通過為每個具有連續/相同Sequence的雇員分配一個唯一的ROW_NUMBER來對相同的Sequence進行分組：
SELECT *
, GRP = ROW_NUMBER() OVER（PARTITION BY Employee ORDER BY [Start] ) - ROW_NUMBER() OVER(PARTITION BY Employee, [序列] 訂單 BY [開始] )
INTO #cte_grp
FROM #cte_sequence
最后我們計算合同期限的min和max：
SELECT *
, MIN([Start]) OVER(PARTITION BY Employee, GRP） AS ContractStart
, CASE WHEN COUNT（*) OVER(PARTITION BY Employees, GRP) = COUNT([End] )
OVER(PARTITION BY Employee, GRP) THEN MAX([End]) OVER(PARTITION BY Employee, GRP） ELSE NULL END AS ContractEnd
FROM cte_grp
COUNT(*)和COUNT([End])的比較是必要的，否則ContractEnd將是最大的非NULL值，即2018-02-01。
整個代碼與CTEs在這里：
WITH cte_delta AS (
SELECT *
, DeltaPrev = DATEDIFF(DAY, LAG([End], 1, NULL) OVER(PARTITION BY) keyword">BY雇員ORDER BY [Start]）。) [Start]) --不相關（？）。
, DeltaNext = DATEDIFF(DAY, [End], LEAD([Start], 1, NULL) OVER(PARTITION BY) keyword">BY員工 ORDER BY [Start] )
FROM #input_table
)
, cte_sequence AS (
SELECT *
, [Sequence] = CASE WHEN DeltaNext > 1 THEN 1 ELSE 2 END
FROM cte_delta
)
, cte_grp AS (
SELECT * .
, GRP = ROW_NUMBER() OVER（PARTITION BY Employee ORDER BY [Start] ) - ROW_NUMBER() OVER(PARTITION BY Employee, [序列] 訂單 BY [開始] )
FROM cte_sequence
)
SELECT *
, MIN([Start]) OVER(PARTITION BY Employee, GRP） AS ContractStart
, CASE WHEN COUNT（*) OVER(PARTITION BY Employees, GRP) = COUNT([End]) OVER(PARTITION BY Employee, GRP) THEN MAX([End]) OVER(PARTITION BY Employee, GRP） ELSE NULL END AS ContractEnd
FROM cte_grp
下面是輸出結果：
請根據您的需要自由選擇DISTINCT記錄。
uj5u.com熱心網友回復：
基本上，你想使用lag()來獲得前一個日期，然后做一個累計的總和。 這看起來像：
select c.*,
sum(case when prev_end > =dateadd(day, -1, start) then 0 else 1
end) over (partition by employee orderby start) as 排名
from (select c.*,
lag(end) over (partition by 員工 order by start) as prev_end
from contracts c
) c;
你提到，你可能想重新計算新的start和end。 你只需將上述內容作為一個子查詢/CTE，并在employee和ranking上進行聚合。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/310892.html
標籤：
上一篇：如何將字符推入字串堆疊？