Oracle SQL – Selecting first inspection row after delivery row

I am struggling with the following problem.

The data in tblTrans is below:

| Transaction_ID |    Transaction_Type   | Hours | Employee_ID |
|:--------------:|:---------------------:|:-----:|:-----------:|
|       107      |   In-Place Delivery   |  0.60 |     SDK     |
|       110      |   In-Place Delivery   |  0.88 |     SDK     |
|       112      |       Inspection      |  1.22 |     SDK     |
|       114      |   In-Place Delivery   |  2.11 |     JMK     |
|       115      |       Inspection      |  0.01 |     SDK     |
|       116      |       Inspection      |  0.64 |     JMK     |
|       239      | Out-of-Place Delivery |  0.12 |     JMK     |
|       241      |   In-Place Delivery   |  0.33 |     JMK     |
|       255      | Out-of-Place Delivery |  0.87 |     KWE     |
|       256      |       Inspection      |  5.90 |     JMK     |
|       263      |       Inspection      | 11.80 |     SDK     |
|       291      |   In-Place-Delivery   |  1.00 |     SDK     |
|       292      |       Inspection      |  0.04 |     JMK     |
|       400      | Out-of-Place Delivery |  9.50 |     JMK     |
|       401      |       Inspection      |  1.21 |     JMK     |

What I am trying to accomplish is determining the first inspection transaction after each In-Place Delivery, creating a table that looks like:

| Delivery_Transaction_ID | First_Inspection |
|:-----------------------:|:----------------:|
|           107           |        112       |
|           110           |        112       |
|           114           |        115       |
|           241           |        256       |
|           291           |        292       |

The Transaction_ID for the forthcoming inspection after a delivery will always be greater than the previous deliveries. It goes in order. However, it may not necessarily be +1 as sometimes the system jumps numbers. However, it will always be greater.

I so far have tried a few variants of the below query:

WITH
  In_Place_Deliveries AS (  
      SELECT
        Transaction_ID AS Delivery_Transaction    
      FROM
        tblTrans    
      WHERE
        Transaction_Type = 'In-Place Delivery'  
  ),


  SELECT
    ipd.Delivery_Transaction,
    LEAD(MIN(Transaction_ID)) OVER (ORDER BY Transaction_Type) AS "First_Inspection"
  FROM
    tblTrans
      INNER JOIN In_Place_Deliveries ipd on ipd.Delivery_Transaction = tblTrans.Transaction_ID

But I get:

| DELIVERY_TRANSACTION | First_Inspection |
|:--------------------:|:----------------:|
|          107         |        110       |
|          110         |        114       |
|          114         |        241       |
|          241         |      (null)      |

which is clearly incorrect.

I have set up a SQL FIDDLE here, for demo purposes, with the data and the query.

How can I redesign my query to achieve the desired output?

Answer

Simply using LEAD IGNORE NULLS:

WITH In_Place_Deliveries AS 
 (  
      SELECT
        Transaction_ID AS Delivery_Transaction ,
        Transaction_Type,
        -- next Inspection
        lead(case when Transaction_Type = 'Inspection' then Transaction_ID end ignore nulls)
        OVER (ORDER BY Transaction_ID)
      FROM
        tblTrans    
      WHERE
        Transaction_Type IN ( 'In-Place Delivery', 'Inspection')
  )
SELECT *
FROM In_Place_Deliveries
WHERE Transaction_Type = 'In-Place Delivery'

See fiddle

Advertisement

Answer