sql - 如何循环遍历表来查找数据集？

Question

我必须找到订单生命周期的时间差(以分钟为单位)。即每个订单从收到订单(事件 ID 1)到键入(2)到打印(3)到交付(4)的时间

例如

我完全不知道应该采取哪种方法？ 用例或 if then 语句？类似于 for every 循环遍历每条记录？最有效的方法是什么？

我知道一旦我获得正确变量中的日期，我就可以使用 DATEDIFF。

declare @received as Datetime, @keyed as DateTime, @printed as Datetime, @Delivered as Datetime, @TurnTime1 as int
Select
IF (tblOrderActivity.ActivityID = 1) SET @received = tblOrderActivity.ActivityDate
---
----
from tblOrderActivity
where OrderID = 1 

它应该显示@TurnTime1 = 48分钟，因为orderID 1从接收(事件id 1)到键入(事件id 2)花了48分钟@TurnTime2 = 29分钟，因为订单1从键入(事件id 2)花了29分钟)到每个订单的打印(事件 ID 3)等等

Answer 1

您可以通过旋转数据轻松完成此操作。可以通过两种方式完成。

1.使用条件聚合来透视数据。在pivoting之后，您可以找到不同阶段之间的datediff。试试这个。

SELECT orderid,Received,Keyed,Printed,Delivered,
       Datediff(minute, Received, Keyed)    TurnTime1,
       Datediff(minute, Keyed, Printed)     TurnTime2,
       Datediff(minute, Printed, Delivered) TurnTime3
FROM  (SELECT OrderID,
              Max(CASE WHEN ActivityID = 1 THEN ActivityDate END) Received,
              Max(CASE WHEN ActivityID = 2 THEN ActivityDate END) Keyed,
              Max(CASE WHEN ActivityID = 3 THEN ActivityDate END) Printed,
              Max(CASE WHEN ActivityID = 4 THEN ActivityDate END) Delivered
       FROM   Yourtable
       GROUP  BY OrderID)A 

2.使用Pivot转置数据

SELECT orderid,
       [1]                        AS Received,
       [2]                        AS Keyed,
       [3]                        AS Printed,
       [4]                        AS Delivered,
       Datediff(minute, [1], [2]) TurnTime1,
       Datediff(minute, [2], [3]) TurnTime2,
       Datediff(minute, [3], [4]) TurnTime3
FROM   Yourtable
       PIVOT (Max(ActivityDate)
             FOR ActivityID IN([1],[2],[3],[4]))piv