Conditionally perform a lagging MAX and MIN calculation - Oracle SQL

Question

I have a table of data like so:

|   Order   | Step | StepStart |  StepEnd  | ProcessCode |
|:---------:|:----:|:---------:|:---------:|:-----------:|
| 103502090 | 6900 |  2-Jan-19 |  2-Jan-19 |    START    |
| 103502090 | 6900 |  1/2/2019 |  1/2/2019 |     END     |
| 103502090 | 6950 |  2-Jan-19 |  2-Jan-19 |    START    |
| 103502090 | 6950 |  2-Jan-19 |  2-Jan-19 |     END     |
| 103502090 | 7000 |  3-Jan-19 |  3-Jan-19 |    START    |
| 103502090 | 7000 |  3-Jan-19 |  3-Jan-19 |     END     |
| 103502090 | 7050 |  4-Jan-19 |  4-Jan-19 |    START    |
| 103502090 | 7050 |  4-Jan-19 |  4-Jan-19 |     END     |
| 103502090 | 7100 |  8-Jan-19 |  8-Jan-19 |    START    |
| 103502090 | 7100 |  8-Jan-19 |  8-Jan-19 |     END     |
| 103502090 | 7200 |  9-Jan-19 |  9-Jan-19 |    START    |
| 103502090 | 7200 |  9-Jan-19 |  9-Jan-19 |     END     |
| 103502090 | 7300 |  9-Jan-19 |  9-Jan-19 |    START    |
| 103502090 | 7300 |  9-Jan-19 |  9-Jan-19 |     END     |
| 103502090 | 7400 |  9-Jan-19 |  9-Jan-19 |    START    |
| 103502090 | 7400 |  9-Jan-19 |  9-Jan-19 |   PROCESS   |
| 103502090 | 7400 |  9-Jan-19 |  9-Jan-19 |    START    |
| 103502090 | 7400 |  9-Jan-19 |  9-Jan-19 |     END     |
| 103502090 | 7450 | 10-Jan-19 | 10-Jan-19 |    START    |
| 103502090 | 7450 | 10-Jan-19 | 10-Jan-19 |   PROCESS   |
| 103502090 | 7450 | 10-Jan-19 | 14-Jan-19 |    START    |
| 103502090 | 7450 | 10-Jan-19 | 14-Jan-19 |   PROCESS   |
| 103502090 | 7450 | 10-Jan-19 | 14-Jan-19 |    START    |
| 103502090 | 7450 | 10-Jan-19 | 14-Jan-19 |     END     |
| 103502090 | 7550 | 14-Jan-19 | 14-Jan-19 |    START    |
| 103502090 | 7550 | 14-Jan-19 | 14-Jan-19 |     END     |
| 103502090 | 7700 | 16-Jan-19 | 16-Jan-19 |    START    |
| 103502090 | 7700 | 16-Jan-19 | 16-Jan-19 |     END     |
| 103502090 | 7750 | 17-Jan-19 | 17-Jan-19 |    START    |
| 103502090 | 7750 | 17-Jan-19 | 17-Jan-19 |     END     |
| 103561375 | 7450 |  4-Jan-19 |  4-Jan-19 |    START    |
| 103561375 | 7450 |  4-Jan-19 |  4-Jan-19 |   PROCESS   |
| 103561375 | 7450 |  4-Jan-19 |  8-Jan-19 |    START    |
| 103561375 | 7450 |  4-Jan-19 |  8-Jan-19 |   PROCESS   |
| 103561375 | 7450 |  4-Jan-19 |  8-Jan-19 |    START    |
| 103561375 | 7450 |  4-Jan-19 |  8-Jan-19 |     END     |
| 103561375 | 7550 |  8-Jan-19 |  8-Jan-19 |    START    |
| 103561375 | 7550 |  8-Jan-19 |  8-Jan-19 |     END     |
| 103561375 | 7700 |  9-Jan-19 |  9-Jan-19 |    START    |
| 103561375 | 7700 |  9-Jan-19 |  9-Jan-19 |     END     |
| 103561375 | 7750 | 10-Jan-19 | 10-Jan-19 |    START    |
| 103561375 | 7750 | 10-Jan-19 | 10-Jan-19 |     END     |
| 103561454 | 6106 | 31-Jan-19 | 31-Jan-19 |    START    |
| 103561454 | 6106 | 31-Jan-19 | 31-Jan-19 |     END     |
| 103561454 | 6111 |  4-Feb-19 |  4-Feb-19 |    START    |
| 103561454 | 6111 |  4-Feb-19 |  4-Feb-19 |     END     |
| 103561454 | 6900 |  4-Feb-19 |  4-Feb-19 |    START    |
| 103561454 | 6900 |  4-Feb-19 |  4-Feb-19 |     END     |
| 103561454 | 6950 |  4-Feb-19 |  4-Feb-19 |    START    |
| 103561454 | 6950 |  4-Feb-19 |  4-Feb-19 |     END     |
| 103561454 | 7000 |  4-Feb-19 |  4-Feb-19 |    START    |
| 103561454 | 7000 |  4-Feb-19 |  4-Feb-19 |     END     |
| 103561454 | 7050 |  5-Feb-19 |  5-Feb-19 |    START    |
| 103561454 | 7050 |  5-Feb-19 |  5-Feb-19 |     END     |
| 103561454 | 7100 |  6-Feb-19 |  6-Feb-19 |    START    |
| 103561454 | 7100 |  6-Feb-19 |  6-Feb-19 |     END     |
| 103561454 | 7200 |  9-Feb-19 |  9-Feb-19 |    START    |
| 103561454 | 7200 |  9-Feb-19 |  9-Feb-19 |     END     |
| 103561454 | 7300 |  9-Feb-19 |  9-Feb-19 |    START    |
| 103561454 | 7300 |  9-Feb-19 |  9-Feb-19 |     END     |
| 103561454 | 7400 |  9-Feb-19 |  9-Feb-19 |    START    |
| 103561454 | 7400 |  9-Feb-19 |  9-Feb-19 |     END     |
| 103561454 | 7450 | 11-Feb-19 | 11-Feb-19 |    START    |
| 103561454 | 7450 | 11-Feb-19 | 11-Feb-19 |   PROCESS   |
| 103561454 | 7450 | 11-Feb-19 | 14-Feb-19 |    START    |
| 103561454 | 7450 | 11-Feb-19 | 14-Feb-19 |     END     |
| 103561454 | 7550 | 14-Feb-19 | 14-Feb-19 |    START    |
| 103561454 | 7550 | 14-Feb-19 | 14-Feb-19 |     END     |
| 103561454 | 7700 | 16-Feb-19 | 16-Feb-19 |    START    |
| 103561454 | 7700 | 16-Feb-19 | 16-Feb-19 |     END     |
| 103561454 | 7750 | 18-Feb-19 | 18-Feb-19 |    START    |
| 103561454 | 7750 | 18-Feb-19 | 18-Feb-19 |     END     |

The goal would be:

For every order, and every step in every order, calculate its queue time which is the time from the previous step's LAST END DATE to the current step's FIRST START DATE (the time this step was in queue to be performed).

So basically, for each row Order and Step combination, I need to find the last END date (when ProcessCode = END) and the first START date (when ProcessCode = START). Then, subtract those two and report back the value.

I have tried:

SELECT
    Order,
    Step,
    (MAX(StepEnd) OVER PARTITION BY (Order, Step) - LAG(MIN(StepStart) OVER PARTITION BY (Order, Step), 1, null)) OVER PARTITION BY (
    Order, Step) AS QueueTime

FROM
    thetable

But keep getting errors related to missing right parenthesis.

How am I able to write a query to achieve results like, for example, Step 7450 would be 1. (These are actually datetime fields in the real database but when I edited some results it turned to just date)

Answer 1

Your syntax regarding parenthesis for over partition by is slightly wrong, should be:

MAX("StepEnd") OVER (PARTITION BY "Order", "Step")
MIN("StepStart") OVER (PARTITION BY "Order", "Step")

I have tried to fix your statement, see below:

SELECT
    "Order",
    "Step", 
    MAX("StepEnd") -
    LAG(MIN("StepStart"), 1, NULL) OVER (ORDER BY MIN("StepStart")) AS QueueTime
    FROM
  Table1
group by "Order","Step"

You can check my results here in a fiddle.

Some useful examples for the Lag function can be found here. It seems it always needs an order by.