如何在SQL Server 2008中计算记录之间的时间

Question

在SQL 2008中，对于一张审计表，我需要计算每个订单在给定步骤中停留的时间（表示为新列）。

 Old      New           Time Entered        Order Number
 NULL     Step 1        4/30/12 10:43       1C2014A
 Step 1   Step 2        5/2/12 10:17        1C2014A
 Step 2   Step 3        5/2/12 10:28        1C2014A
 Step 3   Step 4        5/2/12 11:14        1C2014A
 Step 4   Step 5        5/2/12 11:19        1C2014A
 Step 5   Step 9        5/3/12 11:23        1C2014A
 NULL     Step 1        5/18/12 15:49       1C2014B
 Step 1   Step 2        5/21/12 9:21        1C2014B
 Step 2   Step 3        5/21/12 9:34        1C2014B
 Step 3   Step 4        5/21/12 10:08       1C2014B
 Step 4   Step 5        5/21/12 10:09       1C2014B
 Step 5   Step 6        5/21/12 16:27       1C2014B
 Step 6   Step 9        5/21/12 18:07       1C2014B
 NULL     Step 1        6/12/12 10:28       1C2014C
 Step 1   Step 2        6/13/12 8:36        1C2014C
 Step 2   Step 3        6/13/12 9:05        1C2014C
 Step 3   Step 4        6/13/12 10:28       1C2014C
 Step 4   Step 6        6/13/12 10:50       1C2014C
 Step 6   Step 8        6/13/12 12:14       1C2014C
 Step 8   Step 4        6/13/12 15:13       1C2014C
 Step 4   Step 5        6/13/12 15:23       1C2014C
 Step 5   Step 8        6/13/12 15:30       1C2014C
 Step 8   Step 9        6/18/12 14:04       1C2014C

• 这些步骤不需要按顺序进行，因此第一步可能在第五步之后发生。
• 订单的记录不是按步骤或订单顺序依次存储的，而是根据输入时间与其他订单混合存储的。按订单编号和新状态排序的示例数据不正常，不能依赖。
• 对于任何给定的订单，每个步骤都可以重复执行，如果为订单重复执行，则将每个步骤的时间相加。
• 起始步骤记录始终为空列中的值。
• 起始步骤计算为给定订单的新列中的值与其旧列中的值之间的时间差。

输出可以简单地为：

Order Number   Step      Time in Step
1C2014A        Step 1    6:09

- sreeli

---

你能描述一下输出应该是什么样子的吗？ - Cristian Lupascu

你从哪里得到了6:09（即：根据你的数值，你是如何得出那个数字的）？5/2/12 10:17 - 4/30/12 10:43超过一天。 - NullUserException

你是在要求别人为你解决这个问题，还是你有一个特定的问题或疑问可以被解决？ - Jim

抱歉，6:09只是一个示例数字，并不是实际结果。我只是想快速得到一些东西。 - sreeli

解决问题当然是好的，但对于方法的建议也会有所帮助。我真的不知道该如何着手处理这个问题？ - sreeli

Answer 1

这是我想出来的：

select 
  a1.OrderNumber,
  a1.New as Step, 
  datediff(second, a1.TimeEntered, isnull(a2.timeEntered,getdate()))
    as [Time in Step (seconds)]
from AuditTrail a1
left join AuditTrail a2
  on a1.New = a2.Old 
  and a1.OrderNumber = a2.OrderNumber

对于那些出不了订单的步骤，时间被计算到当前时刻（getdate()）

在线工作示例：http://www.sqlfiddle.com/#!3/fbaff/11

更新：

上面的查询可以多次显示一个步骤（例如：订单 1C2014C 多次经过第4步）。

要按订单/步骤分组并显示每个这样的对应项的总时间，请改用此 SQL 语句：

select
  a1.OrderNumber,
  a1.New as Step, 
  sum(datediff(second, a1.TimeEntered, isnull(a2.timeEntered,getdate())))
    as [Total Time in Step (seconds)]
from AuditTrail a1
left join AuditTrail a2
  on a1.New = a2.Old 
  and a1.OrderNumber = a2.OrderNumber
group by a1.OrderNumber, a1.New
order by a1.OrderNumber

在线工作示例：http://www.sqlfiddle.com/#!3/fbaff/14

- Cristian Lupascu