如何根据工作类型从datetime列中获取时间跨度

Question

我有一个表，里面有输入的日期时间和输入类型我需要根据"工作"直到"暂停"获得HH:mm持续时间然后获得"暂停"持续时间，直到下一个"工作">

ClickEvent              TypeOfWork
----------------------------------
2021-03-09 19:50:30.560 Work
2021-03-09 19:50:41.793 Work
2021-03-09 19:50:52.780 Pause
2021-03-09 19:51:00.840 Work
2021-03-09 19:51:24.640 Work
2021-03-09 19:51:25.973 Work
2021-03-09 19:51:26.387 Work
2021-03-09 19:51:26.710 Work
2021-03-09 19:51:27.327 Work
2021-03-09 19:51:27.933 Work
2021-03-09 19:51:33.827 Work
2021-03-09 19:53:57.443 Work
2021-03-09 19:53:58.557 Work
2021-03-09 19:53:59.133 Work

所以我的输出应该是：

Work           Pause
---------------------------------
00:03          00:00

Answer 1

尝试以下操作：

首先是一个CTE，通过识别每种工作类型的更改位置，重新设置每种更改的行计数来简化问题。

然后，仅使用行号为1的行，计算当前和下一次单击事件之间的差异。

你可以在这里停下来，依次显示在每个点击事件块中花费的时间，但最后用一个外部总和来表示每个TypeOfWork花费的总时间。

你的样本数据(在你更改它之前的原始图像中(显示了多个TypesOfWork(例如work/pause/stop；工作类型"；。

;with r as (
select TypeOfWork,ClickEvent,Row_Number() over (partition by TypeOfWork,rn - rnDiff order by rn) RowNum 
from (
select TypeOfWork, ClickEvent,
Row_Number() over (order by ClickEvent asc) rn ,Row_Number() over (partition by TypeOfWork order by ClickEvent asc) rnDiff
from [Table]
)a
)
select TypeOfWork, Convert(varchar, DateAdd(ss,Sum(EventTot), 0), 114) Duration
from (
select TypeOfWork, ClickEvent,
DateDiff(ss, ClickEvent, Lead(ClickEvent,1) over(order by ClickEvent)) EventTot
from r
where RowNum=1
)c
group by TypeOfWork