I have a database table that is structured as follows:

This table is logging the status of work order operations by equipment. Each operation can be started and stopped multiple times. The log also tracks rework events on the operations which I need to treat as "stopped events. What I need to do is created a separate table that is a log of the "stopped" events that contains the start time of that event based on the "Started" event in the log, ie. something that looks like this:

If I didn't have to deal with the multiple "Complete" events pointing to a singe "Start" event I would be able to just use a LEAD function but cannot in this case. I am at a loss of how to approach this issue?

CodePudding user response：

This is the sort of problem that is perfect for window functions. There is no need to self-join, you just need to look at all the previous rows, using ROW UNBOUNDED PRECEDING.

We can use a conditional windowed MAX to solve this particular problem

SELECT
  [order],
  operation,
  equipment,
  status,
  startTime,
  endTime = timeRecorded
FROM (
    SELECT *,
      startTime = MAX(CASE WHEN status = 'Started' THEN timeRecorded END)
          OVER (PARTITION BY [order] ORDER BY timeRecorded ROWS UNBOUNDED PRECEDING)
    FROM Log
) Prev
WHERE status <> 'Started';

db<>fiddle

You may want to adjust the PARTITION BY clause.

CodePudding user response：

You essentially want to join "started" operations to ALL of their corresponding "non-started" events (I'm assuming that last bit is true, i.e. there is no status value other than 'started' that should not be included in the completed operations - if this isn't the case, you'd want to modify the statement below to filter the inner subquery on completed only statuses.

Assuming that you're matching "operations" on a combination of order, equipment, and operation values, and that you also want to include "started" records that have no corresponding "completed" operation yet at all, something like the following should do the trick (if my assumptions are correct or close):

SELECT  os.[order], os.equipment, os.operation,
        oc.status AS completion_status,
        os.timeRecorded AS startTime,
        oc.timeRecorded AS endTime
FROM    ops os
LEFT JOIN
        (
        SELECT  oci.*
        FROM    ops oci
        WHERE   oci.status != 'Started'
        ) oc
ON      os.[order] = oc.[order]
        AND os.equipment = oc.equipment
        AND os.operation = oc.operation
WHERE   os.status = 'Started'
ORDER BY
        os.[order] DESC, os.equipment, os.operation DESC,
        os.timeRecorded DESC, oc.timeRecorded DESC;

Which should produce something like the following (using your sample data from above):

order       e       o       status          startTime                   endTime
4321    |   C   |   4   |   Complete    |   2021-11-08 05:01:00.000 |   2021-11-08 09:01:58.000
1234    |   A   |   2   |   Complete    |   2021-11-08 12:02:30.000 |   2021-11-08 13:02:36.000
1234    |   A   |   1   |   Rework      |   2021-11-08 09:07:02.000 |   2021-11-08 11:02:36.000
1234    |   A   |   1   |   Rework      |   2021-11-08 09:07:02.000 |   2021-11-08 11:01:23.000
678     |   C   |   3   |   Incomplete  |   2021-11-08 06:33:30.000 |   2021-11-08 10:06:24.000
123     |   B   |   1   |   Complete    |   2021-11-08 08:42:17.000 |   2021-11-08 11:51:07.000