代码之家 › 专栏 › 技术社区 › nordscan

mssql:计算启动和停止次数

sql-server

nordscan · 技术社区 · 6 年前

是否可以在SQL中执行此操作:

有记录的表格:

    ID      LaborID     OrderNr      OrderStatusID  OrderStatusDate


    12990   3731573     OPT1814378          2       2018-05-28 09:35:30.123
    13105   230687389   OPT1814378          1       2018-05-29 10:32:14.850
    13106   230687389   OPT1814378          2       2018-05-29 10:52:14.403
    13123   230480202   OPT1814378          1       2018-05-29 13:18:05.233
    13130   230480202   OPT1814378          0       2018-05-29 13:29:17.360
    12837   3731573     OPT1814089          2       2018-05-25 20:28:24.817
    12906   10138504    OPT1814089          1       2018-05-26 10:41:18.680
    12909   10138504    OPT1814089          2       2018-05-26 10:57:40.733
    12913   10138504    OPT1814089          1       2018-05-26 11:41:48.387
    12920   10138504    OPT1814089          0       2018-05-26 12:15:48.590

哪里

OrderStatusID   
0 - End
1 - Begin
2 - pause

需要计算工作时间 from begin to pause (1->2) 或 from begin to end (1->0) 。我的问题是我必须遵守一些条件:

如果第一条记录是 2 然后忽略

工作总是从 1

但可以有更多的停顿 (1->2) 上次工作结束记录每次 0

这种情况下的结果是:

OPT1814378          230687389   00:20:00      
OPT1814378          230480202   00:11:12   
OPT1814089          10138504    00:16:12
OPT1814089          10138504    00:34:00

2 回复 | 直到 6 年前

Squirrel 6 年前

希望这没那么难看。

; with 
cte as
(
    -- CTE for generating a sequence no
    select  *, rn = row_number() over (partition by OrderNr 
                                           order by OrderStatusDate)
    from    @table
),
cte2 as
(
    -- Clean up invalid any rows and regenerate new sequence no
    select  ID, LaborID, OrderNr, OrderStatusID, OrderStatusDate, 
            rn = row_number() over (partition by OrderNr 
                                        order by OrderStatusDate)
    from    cte
    where   (rn = 1 and OrderStatusID = 1)
    or      rn  >= 2
)
select  OrderNr, LaborID,
        convert(varchar(10), 
                dateadd(second, 
                        datediff(second, 
                                 min(OrderStatusDate),
                                 max(OrderStatusDate)), 
                        0), 
                108)
from    cte2
group by OrderNr, 
         LaborID, 
         (rn - 1) / 2

(rn - 1) / 2 将给出值0、0、1、1、2、2等,以便将行分组为两行。

Vijay 6 年前

这个也行

;WITH CTE
AS ( 
    SELECT row_number() OVER ( PARTITION BY ordernr ORDER BY id ) RN ,* FROM test_ti 
    )

,cte2
AS (
    SELECT *
    FROM cte c1
    WHERE NOT EXISTS (
            SELECT *
            FROM cte c2
            WHERE c1.id = c2.id
                AND c2.rn = 1
                AND c2.orderstatusid = 2
            )
    )
SELECT OrderNr
    ,LaborId
    ,TimeInterval
FROM ( SELECT DateDiff(MI, TIME, NxtTm) TimeInterval ,*
    FROM (
        SELECT * ,lead(TIME) OVER ( ORDER BY id ) NxtTm
        FROM cte2
        ) x
    WHERE orderstatusid <> 0
    ) y
WHERE orderstatusid = 1