Selecting values in a partition where a condition is met (Teradata SQL)

Question

I have table that is in the following format:

Number	Departing	Arriving	Departure Time	Removal Indicator
1	Miami	Dallas	1 PM	N
1	Chicago	Dallas	3 PM	Y
1	Dallas	Miami	5 PM	N

In this partition, I want to add another column called Next Departure time.

For the first row, the arriving column = Dallas. I want the next departure time where the departing column = Dallas.

So the desired output for the first row will have an extra column called next departure time with a value of 5 PM because the next Dallas departing value has a departure time of 5 PM.

So I need the query to search through the partition and find the departure time of the next row where the arriving column = departing column.

I have tried something like this:

MIN(CASE WHEN Arriving = Departing THEN Departure Time) OVER (PARTITION BY Number ORDER BY Departure Time ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) 

but this returns null since SQL must search through the partition first to find a case where arriving = departing.

Answer 1

You're trying to match different rows in the table, so despite the fact that you're dealing with a single table, it would be convenient to think about it as two copies of the same table. If we think about it like that, the query pretty much lands itself.

Assuming your table's name is Tab, and that there is at most one candidate for the NextDepartureTime column for each row, the query could be something like this:

SELECT
    a.*,
    b.DepartureTime as NextDepartureTime
FROM
    Tab a
    LEFT JOIN Tab b
        ON a.Arriving = b.Departing