我有一個包含日期、人員和狀態列的歷史記錄表，我需要知道從開始到完成狀態所花費的總時間是多少（完成狀態可能會出現多次）。我需要從第一次創建 datediff 到第一次完成狀態，之后我需要獲取下一個日期（如果它沒有完成）并使用再次完成的日期再次獲取 datediff 等等。另一個條件是僅當更改狀態的人不為空時才進行此計算。之后，我需要總結所有時間并得到總數。
我嘗試了 Lead 和 Lag 函式，但沒有得到我需要的結果。
uj5u.com熱心網友回復：
首先讓我們談談提供演示資料。這是一個很好的方法：創建一個類似于您的實際物件的表變數，然后填充它們：
DECLARE @statusTable TABLE (Date DATETIME, Person INT, Status NVARCHAR(10), KeyID NVARCHAR(7))
INSERT INTO @statusTable (Date, Person, Status, KeyID) VALUES
('2022-10-07 07:01:17.463', 1, 'Start', 'AAA-111'),
('2022-10-07 07:01:17.463', 1, 'Waiting', 'AAA-111'),
('2022-10-11 14:01:44.463', 1, 'Waiting', 'AAA-111'),
('2022-10-14 10:04:17.463', 1, 'Waiting', 'AAA-111'),
('2022-10-14 10:04:17.463', 1, 'Finished','AAA-111'),
('2022-10-14 10:04:17.463', 1, 'Waiting', 'AAA-111'),
('2022-10-17 17:01:17.463', 1, 'Waiting', 'AAA-111'),
('2022-10-21 11:03:17.463', 1, 'Waiting', 'AAA-111'),
('2022-10-21 11:03:17.463', 1, 'Finished','AAA-111'),
('2022-10-21 11:03:17.463', 1, 'Waiting', 'AAA-111'),
('2022-10-21 11:04:17.463', NULL, 'Waiting', 'AAA-111'),
('2022-10-21 11:05:17.463', 1, 'Finished','AAA-111')
您的問題是遞回的，因此我們可以使用 rCTE 來解決它。
;WITH base AS (
SELECT *, CASE WHEN LAG(Status,1) OVER (PARTITION BY KeyID ORDER BY Date) <> 'Waiting' AND Status = 'Waiting' THEN 1 END AS isStart, ROW_NUMBER() OVER (PARTITION BY KeyID ORDER BY Date) AS rn
FROM @statusTable
), rCTE AS (
SELECT date AS startDate, date, Person, Status, KeyID, IsStart, rn
FROM base
WHERE isStart = 1
UNION ALL
SELECT a.startDate, r.date, r.Person, r.Status, a.KeyID, r.IsStart, r.rn
FROM rCTE a
INNER JOIN base r
ON a.rn 1 = r.rn
AND a.KeyID = r.KeyID
AND r.IsStart IS NULL
)
SELECT StartDate, MAX(date) AS FinishDate, KeyID, DATEDIFF(MINUTE,StartDate,MAX(Date)) AS Minutes
FROM rCTE
GROUP BY rCTE.startDate, KeyID
HAVING COUNT(Person) = COUNT(KeyID)
StartDate FinishDate KeyID Minutes
---------------------------------------------------------------
2022-10-07 07:01:17.463 2022-10-14 10:04:17.463 AAA-111 10263
2022-10-14 10:04:17.463 2022-10-21 11:03:17.463 AAA-111 10139
我們在這里所做的是尋找并標記起點。因為當有一個開始行時，時間戳匹配第一個等待行并且并不總是有一個開始行，我們將使用第一個等待行作為開始標記。然后，我們遍歷并找到該 KeyID 的下一個 Finish 行。
使用它，我們現在可以在 StartDate 上進行分組，Max StatusDate（作為 FinishDate），然后使用 DATEDIFF 來計算差異。
最后，我們將 KeyID 的計數與 Person 的計數進行比較。如果 Person 的值為 NULL，則計數將不匹配，我們將丟棄資料。
uj5u.com熱心網友回復：
select min(date) as start
,max(date) as finish
,datediff(millisecond, min(date), max(date)) as diff_in_millisecond
,sum(datediff(millisecond, min(date), max(date))) over() as total_diff_in_millisecond
from
(
select *
,count(case when Status = 'Finished' then 1 end) over(order by date desc, status desc) as grp
,case when person is null then 0 else 1 end as flg
from t
) t
group by grp
having min(flg) = 1
order by start
| 開始 | 結束 | diff_in_millisecond | total_diff_in_millisecond |
|---|---|---|---|
| 2022-10-07 07:01:17.4630000 | 2022-10-14 10:04:28.4730000 | 615791010 | 1242093518 |
| 2022-10-14 10:04:28.4730000 | 2022-10-21 11:03:06.7170000 | 608318244 | 1242093518 |
| 2022-10-26 12:46:14.7730000 | 2022-10-26 17:45:59.0370000 | 17984264 | 1242093518 |
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/528852.html
下一篇：將多行轉置為單行