Geek Answers Handbook

How to loop through a table to find a dataset?

I need to find the timediff in minutes for the service life of the order. that is, after receiving the order (action identifier 1) to enter (2) to print (3) before the delivered (4) for each order

eg,

I completely lost on which approach should I take ??? or if then an expression? something like everyone to loop through every record? What should be the most effective way to do this?

I know, as soon as I get the dates in the 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 minutes, since orderID 1 took 48 minutes from the received (activity ID 1) to enter the key (activity ID 2) @ TurnTime2 = 29 minutes, since it took 29 minutes to order 1 from the key (activity ID 2) for printing (activity identifier 3), etc., etc. for each order

+4
source share
4 answers

You can easily do this with pivotingdata. There are two ways to do this.

1.Use Conditional Aggregateto rotate data. After pivotingyou can find datediffbetween different stages. Try it.

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 Pivotto transfer 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
+4
source

, CASE

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
+1

(CTE_Orders).

, ActivityID, OUTER APPLY. , ( ), OUTER APPLY NULL . , , , , . -, .

, Activity ID. Order ID Activity ID, , , ORDER BY SELECT 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;

:

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

, ( 4 - 1).

, , , , , .

+1

this one should fill your needs, but I would suggest using this query when you insert values ​​into a table and directly add that value to 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
0
source

More articles:


All Articles