Create a query to identify whether a ticket is one-way or roundtrip-CodePudding

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:

If there is only one coupon for a ticket, it's automatically considered one-way.
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 (duplicate table of Flights table) but I do not know where to contain the previous/next destinations.

TicketNumber    Coupon  Origin  Destination Value
1000    1   USA JPN One-way
1001    1   JPN USA One-way
1002    1   CAN USA Roundtrip
1002    2   USA CAN Roundtrip
1002    3   CAN BAH One-way
1002    4   BAH USA One-way
1003    1   BRA COL One-way
1003    2   COL MEX Roundtrip
1003    3   MEX COL Roundtrip
1004    1   KOR IND One-way
1005    1   KOR JPN One-way
1005    2   JPN USA One-way
1005    3   USA KOR One-way
1006    1   IND GBR Roundtrip
1006    2   GBR IND Roundtrip
1007    1   CHN JPN One-way
1008    1   SWE DEN Roundtrip
1008    2   DEN SWE Roundtrip
1009    1   MEX USA One-way
1009    2   USA CAN One-way
1010    1   FRA GBR One-way

Can you help me with this?

CodePudding user response：

Sample table is taken as in below image

enter image description here

SQL SCRIPT:

Below script is used for finding if it is one-way trip or round-trip as per specified logic.

select *,
CASE
WHEN MAX(coupon) OVER (PARTITION BY ticketno)=1 then 'ONE-WAY' 
WHEN dest=lead(origin,1) over (partition by ticketno order by coupon) AND origin=lead(dest,1) over (partition by ticketno order by coupon) then 'ROUND-TRIP' 
WHEN origin=lag(dest,1) over (partition by ticketno order by coupon) AND dest=lag(origin,1) over (partition by ticketno order by coupon) then 'ROUND-TRIP'    
ELSE 'ONE-WAY' 
END AS Value
from TABLE1

Output table :

enter image description here

CodePudding user response：

If I understand your question correctly maybe the solution could be something like this?

Round trips are those rows, for which the table contains a row that is identical, but with origin and destination flipped and coupon either 1 higher or 1 lower.

UPDATE flights
SET value = 'Roundtrip'
WHERE 
    (ticketnumber, coupon, origin, destination) IN (
    SELECT ticketnumber, coupon   1, destination, origin
    FROM flights UNION
    SELECT ticketnumber, coupon - 1, destination, origin
    FROM flights)