how to loop thru a table to find data set?

问题:

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

for eg

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?

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 

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

回答1:

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

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.use Pivot to transpose the data

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 

回答2:

As you mentioned in your question, one possible way is to use CASE statement

DECLARE @i INT, @max INT
SELECT @i = MIN(OrderId) FROM tblOrderActivity
SELECT @max = MAX(OrderId) from tblOrderActivity

WHILE @i <= @max
BEGIN
SELECT OrderId
       ,ActivityID
       ,ActivityDate
       ,CASE
        WHEN ActivityID = 1 THEN DATEDIFF(MINUTE, ActivityDate, (SELECT ActivityDate FROM C WHERE ActivityID = 2 AND OrderId = @i))
        END AS tokeyed
       ,CASE
        WHEN ActivityID = 2 THEN DATEDIFF(MINUTE, ActivityDate, (SELECT ActivityDate FROM C WHERE ActivityID = 3 AND OrderId = @i))
        END AS toprinted
       ,CASE
        WHEN ActivityID = 3 THEN DATEDIFF(MINUTE, ActivityDate, (SELECT ActivityDate FROM C WHERE ActivityID = 4 AND OrderId = @i))
        END AS todelivered
FROM tblOrderActivity

SET @i = @i + 1
END

回答3:

At first I make a list of all orders (CTE_Orders).

For each order I get four dates, one for each ActivityID using OUTER APPLY. I assume that some activities could be missing (not completed yet), so OUTER APPLY would return NULL there. When I calculate durations I assume that if activity is not in the database, it hasn't happened yet and I calculate duration till the current time. You can handle this case differently if you have other requirements.

I assume that each order can have at most one row for each Activity ID. If you can have two or more rows with the same Order ID and Activity ID, then you need to decide which one to pick by adding ORDER BY to the SELECT inside the OUTER APPLY.

DECLARE @TOrders TABLE (OrderID int, ActivityID int, ActivityDate datetime);

INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (1, 1, '2007-04-16T08:34:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (1, 1, '2007-04-16T08:34:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (1, 2, '2007-04-16T09:22:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (1, 3, '2007-04-16T09:51:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (1, 4, '2007-04-16T16:14:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (2, 1, '2007-04-16T08:34:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (3, 1, '2007-04-16T08:34:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (3, 2, '2007-04-16T09:22:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (3, 3, '2007-04-16T09:51:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (3, 4, '2007-04-16T16:14:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (4, 1, '2007-04-16T08:34:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (4, 2, '2007-04-16T09:22:00');
INSERT INTO @TOrders (OrderID, ActivityID, ActivityDate) VALUES (4, 3, '2007-04-16T09:51:00');

WITH
CTE_Orders
AS
(
    SELECT DISTINCT Orders.OrderID
    FROM @TOrders AS Orders
)
SELECT
    CTE_Orders.OrderID
    ,Date1_Received
    ,Date2_Keyed
    ,Date3_Printed
    ,Date4_Delivered
    ,DATEDIFF(minute, ISNULL(Date1_Received, GETDATE()), ISNULL(Date2_Keyed, GETDATE())) AS Time12
    ,DATEDIFF(minute, ISNULL(Date2_Keyed, GETDATE()), ISNULL(Date3_Printed, GETDATE())) AS Time23
    ,DATEDIFF(minute, ISNULL(Date3_Printed, GETDATE()), ISNULL(Date4_Delivered, GETDATE())) AS Time34
FROM
    CTE_Orders
    OUTER APPLY
    (
        SELECT TOP(1) Orders.ActivityDate AS Date1_Received
        FROM @TOrders AS Orders
        WHERE
            Orders.OrderID = CTE_Orders.OrderID
            AND Orders.ActivityID = 1
    ) AS OA1_Received
    OUTER APPLY
    (
        SELECT TOP(1) Orders.ActivityDate AS Date2_Keyed
        FROM @TOrders AS Orders
        WHERE
            Orders.OrderID = CTE_Orders.OrderID
            AND Orders.ActivityID = 2
    ) AS OA2_Keyed
    OUTER APPLY
    (
        SELECT TOP(1) Orders.ActivityDate AS Date3_Printed
        FROM @TOrders AS Orders
        WHERE
            Orders.OrderID = CTE_Orders.OrderID
            AND Orders.ActivityID = 3
    ) AS OA3_Printed
    OUTER APPLY
    (
        SELECT TOP(1) Orders.ActivityDate AS Date4_Delivered
        FROM @TOrders AS Orders
        WHERE
            Orders.OrderID = CTE_Orders.OrderID
            AND Orders.ActivityID = 4
    ) AS OA4_Delivered
ORDER BY OrderID;

This the result set:

OrderID    Date1_Received             Date2_Keyed                Date3_Printed              Date4_Delivered            Time12    Time23    Time34
1          2007-04-16 08:34:00.000    2007-04-16 09:22:00.000    2007-04-16 09:51:00.000    2007-04-16 16:14:00.000    48        29        383
2          2007-04-16 08:34:00.000    NULL                       NULL                       NULL                       4082575   0         0
3          2007-04-16 08:34:00.000    2007-04-16 09:22:00.000    2007-04-16 09:51:00.000    2007-04-16 16:14:00.000    48        29        383
4          2007-04-16 08:34:00.000    2007-04-16 09:22:00.000    2007-04-16 09:51:00.000    NULL                       48        29        4082498

You can easily calculate other durations, like the total time for the order (time 4 - time1).

Once you have several different queries that produce the same correct result that you need you should measure their performance with your real data on your system to decide which is more efficient.

回答4:

this one should fill your needs, but I would suggest to use this query while you insert the values into the table and directly add this value in a new column

SELECT OrderID,
       ActivityID,
       ActivityDate,
       Datediff(MINUTE, ActivityDate, (SELECT ActivityDate
                                       FROM   [TestDB].[dbo].[tblOrderActivity] AS b
                                       WHERE  b.OrderID = a.OrderID
                                              AND a.ActivityID + 1 = b.ActivityID))
FROM   [TestDB].[dbo].[tblOrderActivity] AS a