CODEWITHSUNDEEP
sql-server
nordscan
nordscan

Reputation: 137

MSSQL: calculate time be many starts and stops

Is it possible to make this in sql:

Have table with records:

    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

where

OrderStatusID   
0 - End
1 - Begin
2 - pause

Need calculate working time from begin to pause (1->2) or from begin to end (1->0). My problem is that there are some conditions that I have to adhere to:

If first record is 2 then ignore

Work begin always with 1

But can have more pause (1->2) The last work end record everytime with 0

The result in this case will be:

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

Upvotes: 0

Views: 32

Answers (2)

Vijay
Vijay

Reputation: 1

This one also works 

;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

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

Hopefully this is not that ugly.

; 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 will gives the value 0, 0, 1, 1, 2, 2 etc for grouping the rows two by two.

Upvotes: 2

Related Questions