MSSQL sort and concatenate data be state-CodePudding

Is it possible to sort data like my example?

I want to concatenate start/stop or start/error state into one row. I tried pivot table, but still don't know how to define it to make it work. Becasue one order can have more errors, or labors, but complete order ends with stop

    ID  LaborID     OrderNr           StateID   State   OrderStatusDate
102610  15784158    OPT2201652              1   start   28.01.2022 08:18
102625  15784158    OPT2201652              0   stop    28.01.2022 09:15
102630  15784158    OPT2201655              1   start   28.01.2022 09:31
102632  15784158    OPT2201655              0   stop    28.01.2022 09:43
101519  10134088    OPT2134651              1   start   11.01.2022 14:42
101524  10134088    OPT2134651              2   error   11.01.2022 15:27
101612  10134088    OPT2134651              1   start   13.01.2022 09:30
101642  10134088    OPT2134651              2   error   13.01.2022 15:01
101783  10134088    OPT2134651              1   start   17.01.2022 12:44
101800  10134088    OPT2134651              0   stop    17.01.2022 14:19
101646  10134088    OPT2134787              1   start   14.01.2022 07:56
101661  10134088    OPT2134787              0   stop    14.01.2022 09:03

To this:

LaborID     OrderNr        start               stop               error              minutes
15784158    OPT2201652     28.01.2022 08:18    28.01.2022 09:15                      0:57
15784158    OPT2201655     28.01.2022 09:31    28.01.2022 09:43                      0:12
10134088    OPT2134651     11.01.2022 14:42                       11.01.2022 15:27   0:45
10134088    OPT2134651     13.01.2022 09:30                       13.01.2022 15:01   5:31
10134088    OPT2134651     17.01.2022 12:44    17.01.2022 14:19                      1:35
10134088    OPT2134787     14.01.2022 07:56    14.01.2022 09:03                      1:07

CodePudding user response：

You don't need to self-join, you can do this in one scan using window functions

WITH NextValues AS (
    SELECT *,
      NextState = LEAD(State)           OVER (PARTITION BY LaborID ORDER BY OrderStatusDate),
      NextDate  = LEAD(OrderStatusDate) OVER (PARTITION BY LaborID ORDER BY OrderStatusDate)
    FROM YourTable t
)
SELECT
  LaborID,
  OrderNr,
  start = OrderStatusDate,
  stop  = CASE WHEN NextState = 'stop'  THEN NextDate END,
  error = CASE WHEN NextState = 'error' THEN NextDate END,
  minutes = DATEDIFF(minute, OrderStatusDate, NextDate)
FROM NextValues t
WHERE State = 'start';

db<>fiddle

CodePudding user response：

You can do that with some CASE or subqueries...

Here is the version with subqueries

SELECT DISTINCT LaborID, OrderNr,
       (SELECT OrderStatusDate FROM T AS Tin WHERE Tin.LaborID = Tout.Labor_ID AND Tin.OrderNr = Tout.OrderNr AND StateID = 1) AS start,
       (SELECT OrderStatusDate FROM T AS Tin WHERE Tin.LaborID = Tout.Labor_ID AND Tin.OrderNr = Tout.OrderNr AND StateID = 0) AS stop,
       (SELECT OrderStatusDate FROM T AS Tin WHERE Tin.LaborID = Tout.Labor_ID AND Tin.OrderNr = Tout.OrderNr AND StateID = 2) AS error
FROM   T AS Tout