SQL Server中特定状态连续记录的时间差

Question

我有一个具有以下结构的表。我正在使用SQL Server 2016

tbody> <<tr>2处理74

pkey	statusDate	Status	StatusEntryID
1	2021-10-07 11:49:59.430	接受	7
	2021-10-07 11:51:50.430
3	2021-10-07 11:52:52.883	派出	7
	2021-10-07 11:55:37.263	发表	7

首先使用条件聚合将4行转换为4列，然后使用COALESCE查找前一状态的时间(必须以相反的顺序完成)。最后使用DATEDIFF:

WITH cte AS (
SELECT StatusEntryID
, t1 = MIN(CASE WHEN Status = 'Accept'     THEN StatusDate END)
, t2 = MIN(CASE WHEN Status = 'Processed'  THEN StatusDate END)
, t3 = MIN(CASE WHEN Status = 'Dispatched' THEN StatusDate END)
, t4 = MIN(CASE WHEN Status = 'Delivered'  THEN StatusDate END)
FROM t
GROUP BY StatusEntryID
)
SELECT StatusEntryID
, Time_Accept    = 0
, Time_Process   = DATEDIFF(SECOND, t1, t2)
, Time_Dispatch  = DATEDIFF(SECOND, COALESCE(t2, t1), t3)
, Time_Delivered = DATEDIFF(SECOND, COALESCE(t3, t2, t1), t4)
FROM cte

所有值都以秒为单位。转换成小时/分/秒是微不足道的。