sql - 关联开始/停止日期并总结它们的持续时间

Question

更新:在底部我添加了我预想的表的预期输出

我的目标是两个相关的停机事件来显示一个事件的因果关系及其导致的停机时间。

我有一个总体架构:

DECLARE @EquipmentDownTime TABLE
(
ID INT IDENTITY(1,1),
Equipment varchar(5),
Time DATETIME,
StartStop NVARCHAR(15)
)

所以一条记录看起来像这样:

 1, Motor, '18/8/2017 12:00:00', Stop 
 2, Motor, '18/8/2017 12:10:00', Start

我目前正在使用匹配对表函数对用户输入的日期范围内的停机总持续时间求和，但我想扩展该功能以根据这些持续时间说明一个事件导致另一个事件的持续时间。

我想做的是关联另一台设备“停止”在上述记录之间的时间，然后确定它的总存在时间。使用下面的示例数据:

INSERT INTO @EquipmentDownTime (Equipment, [Time], StartStop)
VALUES
  ('Motor', '20170812 12:00:00', 'Stop'), --ID 1
  ('Valve', '20170812 12:05:00', 'Stop'), --ID 2
  ('Motor', '20170812 12:30:00', 'Start'), --ID 3
  ('Valve', '20170812 13:05:00', 'Start'), --ID 4
  ('Motor', '20170812 14:00:00', 'Stop'), --ID 5
  ('Valve', '20170812 14:05:00', 'Stop'), --ID 6
  ('Motor', '20170812 15:30:00', 'Start'), --ID 7
  ('Valve', '20170812 15:05:00', 'Start'); --ID 8

在这种情况下，我会说 ID 1 导致 ID 2 停机 1 小时，因为它的停止落在 1 和 3 之间，这是一对停止/开始。

我想最终结果应该是这样的:

 DECLARE @Causation TABLE
(
ID INT                     IDENTITY(1,1),
CauationEquipment          varchar(5),
CauationEquipmentShutdown  DATETIME,
AffectedEquipment          varchar(5),
AffectedEquipmentShutdown  DATETIME,
DurationOfAffectedEquip    INT
)

记录结果(Causation Id和Affected，来自@EquipmentDowntime表中的Identity Id):

 1, Motor, '20170812 12:00:00', Valve, '20170812 12:05:00', 60
 2, Motor, '20170812 14:00:00', Valve, '20170812 14:05:00', 60

我在这里缺少的部分有效地链接了相关性，我想使用一个 while 循环遍历所有停机事件并获取停止和它的下一个开始并将它存储在临时表中，但它变得困惑。

SQL 服务器 2014

Answer 1

假设您使用的是 SQL Server 2012+，并且单个停机时间只有 2 个条目(一个用于停止，一个用于开始):

WITH Downtime AS (
    SELECT *,
           LEAD([Time]) OVER (PARTITION BY Equipment ORDER BY [Time] ASC) AS NextStatus
    FROM @EquipmentDownTime)
SELECT ID,
       Equipment,
       DATEDIFF(MINUTE,[Time],NextStatus) AS Duration
FROM Downtime
WHERE StartStop = 'Stop';