查询以配对交错的开始和结束时间 [英] Query to pair interleaved start and end times

问题描述

我需要一个查询来将开始和结束时间的交错数据配对为一个整洁的输出，但是我的主要障碍是如何在 ProcessTimer 中以交错格式存储数据表。

I need a query to pair interleaved data of 'start' and 'end' times into one neat output, but my main hurdle is with how the data is stored in an interleaved format within the ProcessTimer table.

输入表1

我有一个名为的表具有三列的ProcessTimer 。

• ProcessTimerId INT（主键）


• ProcessTimerDatetime DATETIME


• ProcessTimerAction VARCHAR（5）


• ProcessId INT（FOREIGN KEY）

• ProcessTimerId INT (PRIMARY KEY)
• ProcessTimerDatetime DATETIME
• ProcessTimerAction VARCHAR(5)
• ProcessId INT (FOREIGN KEY)

' ProcessTimerAction '仅设置为'START'或'STOP'，该字段与 ProcessTimerDatetime 列相关联，因为 ProcessTimerDatetime 存储了进程启动或停止的DATETIME值。

'ProcessTimerAction' is only ever set to the value 'START' or 'STOP', which ties in to the 'ProcessTimerDatetime' column, because 'ProcessTimerDatetime' stores a DATETIME value of when a Process has started or stopped.

每个流程都通过 ProcessId 进行标识，并链接到另一个名为 Process 的表，该表包含有关每个流程的一些详细信息。

Each Process is identified via a ProcessId, and that links to another table called Process, which has some details about each Process.

输入表2

Process 表的设置如下：

• ProcessId INT（主键）


• ProcessName VARCHAR（255 ）


• ProcessOwner VARCHAR（255）

• ProcessId INT (PRIMARY KEY)
• ProcessName VARCHAR(255)
• ProcessOwner VARCHAR(255)

从逻辑上讲，流程的START操作应始终跟在STOP操作之后，并成对显示，但是数据中的错误意味着并非每个START操作都总是有STOP操作。在这些情况下，除了显示缺少的STOP条目外，我无能为力

Logically, a Process's START action should always be followed by a STOP action, and display in pairs, however errors in the data mean that there isn't always a STOP action for every START action. In these instances there's nothing I can do but display a NULL for the missing STOP entry

所需的输出

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

I wish to represent the data in grid with the following headings.

• ProcessOwner


• ProcessName


• ProcessStartTime


• ProcessEndTime

• ProcessOwner
• ProcessName
• ProcessStartTime
• ProcessEndTime

有人曾经做到过这样的事情吗？

Has anyone achieved something like this before?

我不确定如何开始创建所需的查询。

I'm unsure how to begin to tackle creating the query I need.

示例数据

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

处理表

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

推荐答案

这里是一个实现：

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

这篇关于查询以配对交错的开始和结束时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！