代码之家 › 专栏 › 技术社区 › Carley Guidry

根据另一列的每个实例的最大日期使用datediff

sql-server sql

Carley Guidry · 技术社区 · 6 年前

我有一个状态表,包含订单号、插入日期和状态。我的目标是确定状态更改之间的持续时间。问题是,如果输入订单的人多次单击状态更改按钮,我将拥有每个状态的多个实例。我举了一个特定订单号的例子:

+------------+---------+--------------------------+
|  orderNO   | status  |       insert_date        |
+------------+---------+--------------------------+
| OBJV107522 | ENTERED | 2/4/2019 11:44:45.800 AM |
| OBJV107522 | ENTERED | 2/4/2019 11:45:14.410 AM |
| OBJV107522 | ENTERED | 2/4/2019 11:45:14.597 AM |
| OBJV107522 | ENTERED | 2/4/2019 11:45:14.833 AM |
| OBJV107522 | OPEN    | 2/4/2019 12:27:15.710 PM |
| OBJV107522 | ENTERED | 2/4/2019 12:36:39.327 PM |
| OBJV107522 | ENTERED | 2/4/2019 12:36:39.920 PM |
| OBJV107522 | OPEN    | 2/4/2019 2:34:58.957 PM  |
| OBJV107522 | ENTERED | 2/4/2019 2:35:07.817 PM  |
| OBJV107522 | OPEN    | 2/4/2019 3:50:04.393 PM  |
+------------+---------+--------------------------+

理想情况下,我希望看到状态更改之间的时间间隔。输出将需要如下所示。

第一行应该是状态更改前所有“已输入”的最大实例减去状态更改前“打开”的第一个实例的最大实例。
下一行应该是第一个“打开”实例的最大值(在状态变回“已输入”之前减去第二个“已输入”的最大实例)。

+------------+------------+-----------------+ | orderNO | New_status | minutes_elapsed | +------------+------------+-----------------+ | OBJV107522 | OPEN | 42 | 4th row - 5th row of the original data | OBJV107522 | ENTERED | 9 | 5th row-7th row of the original data | OBJV107522 | OPEN | 118 | | OBJV107522 | ENTERED | 0 | | OBJV107522 | OPEN | 75 | +------------+------------+-----------------+

我一个人干这件事就无能为力了。我开始让自己相信这是不可能的。有什么建议请告诉我。

2 回复 | 直到 6 年前

Luis Cazares 6 年前

也许像这样利用窗口功能。

WITH CTE AS(
    SELECT *, 
        LAG( status) OVER( PARTITION BY orderNO ORDER BY insert_date) AS Previous_Status, 
        LAG( insert_date) OVER( PARTITION BY orderNO ORDER BY insert_date) AS Previous_Date
    FROM OrderStatus
)
SELECT orderNO,
    status,
    DATEDIFF(mi, Previous_Date, insert_date) AS minutes_elapsed,
    ROUND(DATEDIFF(ss, Previous_Date, insert_date)/60., 0) AS minutes_elapsed2 /*This actually matches your expected results*/
FROM CTE
WHERE status <> Previous_Status;

Slava Murygin 6 年前

DECLARE @t TABLE (orderNO VARCHAR(20),status  VARCHAR(20), insert_date DATETIME)

INSERT INTO @t VALUES
(' OBJV107522 ',' ENTERED ',' 2/4/2019 11:44:45.800 AM '),
(' OBJV107522 ',' ENTERED ',' 2/4/2019 11:45:14.410 AM '),
(' OBJV107522 ',' ENTERED ',' 2/4/2019 11:45:14.597 AM '),
(' OBJV107522 ',' ENTERED ',' 2/4/2019 11:45:14.833 AM '),
(' OBJV107522 ',' OPEN    ',' 2/4/2019 12:27:15.710 PM '),
(' OBJV107522 ',' ENTERED ',' 2/4/2019 12:36:39.327 PM '),
(' OBJV107522 ',' ENTERED ',' 2/4/2019 12:36:39.920 PM '),
(' OBJV107522 ',' OPEN    ',' 2/4/2019 2:34:58.957 PM  '),
(' OBJV107522 ',' ENTERED ',' 2/4/2019 2:35:07.817 PM  '),
(' OBJV107522 ',' OPEN    ',' 2/4/2019 3:50:04.393 PM  ');

SELECT t2.orderNO, t2.status, t2.insert_date
    , minutes_elapsed  = MAX(DATEDIFF(SECOND, t.insert_date, t2.insert_date)/60)
FROM @t as t
CROSS APPLY (
    SELECT insert_date = MIN(t1.insert_date) 
    FROM @t as t1
    WHERE t1.status != t.status 
        and t1.insert_date > t.insert_date
) as tm
INNER JOIN @t as t2 ON t2.insert_date = tm.insert_date 
GROUP BY t2.orderNO, t2.status, t2.insert_date
ORDER BY t2.insert_date