查询与交织在一起的开始和结束时间

Question

我需要一个查询才能将'start'和'end'时间的交错数据与一个整洁的输出相结合，但是我的主要障碍是如何以数据存储在该数据中的形式中。 ProcessTimer 表。

输入表1

我有一个名为 processTimer 的表，带有三列。

• processTimerId int（主键）
• processTimerDateTime dateTime
• processTimeraction varchar（5）
• ProcessID int（外键）

' processTimerAction '仅设置为"开始"或"停止"，该值与' processTimerDateTime '列有关，因为'>processTimerDateTime '存储一个过程启动或停止的日期值。

每个过程都是通过a processID 来标识的，并且链接到称为 Process 的另一个表，该表有有关每个过程的一些详细信息。

输入表2

Process 表设置如下：

• ProcessID int（主键）
• ProcessName varchar（255）
• Processowner varchar（255）

从逻辑上讲，过程的启动操作应始终遵循停止操作，并成对显示，但是数据中的错误意味着每个开始动作并不总是停止操作。在这些情况下，我无能为力，只显示缺失的停止条目

所需的输出

我希望用以下标题表示网格中的数据。

• Processowner
• ProcessName
• ProcessStarttime
• ProcessEndTime

以前有人做过这样的事情吗？

我不确定如何开始解决我需要的查询。

示例数据

processTimer 表

ProcessTimerId,ProcessTimerDatetime,ProcessTimerAction,ProcessId
1,1/1/2017 08:00:34,START,883
2,1/1/2017 08:03:76,STOP,883
3,1/1/2017 08:03:77,START,445
4,1/1/2017 08:03:79,START,636
5,1/1/2017 08:05:77,STOP,445
6,1/1/2017 08:07:34,START,445
7,1/1/2017 08:09:23,START,445
8,1/1/2017 08:12:61,STOP,636
9,1/1/2017 08:14:65,STOP,445

Process 表

ProcessId,ProcessName,ProcessOwner
445,CTC hourlies,Sarah Parkes
636,Garage import,John Dean
883,DF task,Kate Duke

Answer 1

这是一个实现：

WITH ProcessTimerWithRowNum([ProcessId], [ProcessTimerAction], [ProcessTimerDatetime],[rno])
AS
( SELECT [ProcessId],
         [ProcessTimerAction],
         [ProcessTimerDatetime], 
         ROW_NUMBER() OVER(PARTITION BY ProcessId ORDER BY ProcessTimerId) AS [rno] 
  FROM  ProcessTime )
SELECT PT.[ProcessId], 
       P.[ProcessOwner], 
       P.[ProcessName], 
       PT.[ProcessTimerDatetime] AS StartTime, 
       DPT.ProcessTimerDatetime AS EndTime FROM
ProcessTimerWithRowNum PT 
LEFT JOIN
ProcessTimerWithRowNum DPT
ON PT.ProcessId = DPT.ProcessId  AND DPT.ProcessTimerAction = 'STOP' AND DPT.rno = PT.rno + 1
INNER JOIN Process P ON PT.ProcessId = P.ProcessId
WHERE PT.ProcessTimerAction = 'START'

以上数据输出：

ProcessOwner | ProcessName | StartTime | EndTime  
445 |Sarah Parkes|  CTC hourlies|   1/1/2017 08:03:77|  1/1/2017 08:05:77
445 |Sarah Parkes|  CTC hourlies|   1/1/2017 08:07:34|  NULL
445 |Sarah Parkes|  CTC hourlies|   1/1/2017 08:09:23|  1/1/2017 08:14:65
636 |John Dean| Garage import|  1/1/2017 08:03:79|  1/1/2017 08:12:61
883 |Kate Duke| DF task|    1/1/2017 08:00:34|  1/1/2017 08:03:76