SQL: Query for flights table

Question

Hi everyone,

I have a flights table and I need help on identifying whether the ticket number is one-way or round trip as seen in the image.

Here are the specifications I need to satisfy:

1. If there is only one coupon for a ticket, it's automatically considered one-way.
2. If the next origin is equivalent to current destination AND the current origin is the same as the next destination, this should be "roundtrip". Given that they have the same ticket number and the coupon >= 1.

I am actually having a JOIN statement from the two table but I do not know where to contain the previous/next destinations.

Can you help me with this?

Answer 1

Check a query:

select *,  
    case when   
        ( Origin = lag(Destination) over (partition by TicketNumber order by Coupon) and   
          Destination = lag(Origin) over (partition by TicketNumber order by Coupon))  
        or  
        ( Origin = lead(Destination) over (partition by TicketNumber order by Coupon) and  
          Destination = lead(Origin) over (partition by TicketNumber order by Coupon))  
    then 'Roundtrip'  
    else 'One-way' end as [Value]  
from Flights  
order by TicketNumber, Coupon  

Answer 2

Hi @Viorel this works thank you