如何遍历表以查找数据集?

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

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

I have to find the timediff in minutes for a order lifetime. i.e time from order was received(Activity ID 1) to keyed(2) to printed(3) to delivered(4) for each order

例如

我完全迷失了应该采取哪种方法?用例或 if then 语句 ??类似于每个循环遍历每个记录?最有效的方法是什么?

I am completely lost at which approach should i take?? use case or if then statement ?? something like for each to loop thru each record? what should be the most efficient way to do it?

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

i know once i get dates in correct variables i can use 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 分钟，因为从键控(活动 ID 2)订购 1 花了 29 分钟) 为每个订单打印(活动 ID 3)等等

it should show me @TurnTime1 = 48 mins as orderID 1 took 48 mins from received(activity id 1) to keyed (activity id 2) @TurnTime2 = 29 mins as it took 29mins for order 1 from keyed(activity id 2) to printed (activity id 3) so on and so forth for each order

推荐答案

您可以通过透视数据轻松完成此操作.可以通过两种方式完成.

You can do this easily by pivoting the data.It can be done in two ways.

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

1.Use Conditional Aggregate to pivot the data. After pivoting you can find datediff between different stages. Try this.

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

相关文章