問題描述

我必須以分鐘為單位找到訂單生命周期的時間差異.即每個訂單從收到訂單(活動 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 

這篇關于如何遍歷表以查找數據集?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！