Need a SQL Server query to eliminate the Highlighted Rows ( Returning Routes in Flight)

I have a requirement where I need to eliminate all the rows which have returning flight routes.

I have highlighted the routes in the orange color which depicts this behavior.

For example, row 1 has one route BKI - MYY and row 4 has MYY - BKI. I need a flag (Boolean 1/0) that checks the entire row and sees if it's a returning flight or not.

Note: there are a few scenarios where the flight started at 10:00 PM night today and returned the next day early morning. if it is a consecutive day then we need to eliminate that rows as well.

Any thought on how to solve this?

Gsheet link : Sample Data : https://docs.google.com/spreadsheets/d/1ibrUfOTMEx4i-erDQnIfszdcEvJKU_oFcnMBFcGbvyM/edit?usp=sharing

Definition: Return flight is a two-way journey by air. If a flight is starting from the source and after certain hop/route returns backs to the same source (Departure Station)

with AllFlight as  (  select row_number()over(order by std_utc)rn, aoc,hub,flight ,departure,arrival,std_utc,sta_utc  from TABLENAME01  ),  returnFlight as   (  select * from AllFlight s  where exists   (      select * from AllFlight s2       where s2.rn<s.rn and s2.arrival=s.departure and s2.departure=s.arrival       and       (date_diff(EXTRACT(DATE FROM s.sta_utc),EXTRACT(DATE FROM s2.std_utc), DAY))<=1  )  )    select aoc,hub,flight ,departure ,arrival,std_utc  ,sta_utc from AllFlight where rn not in (select rn from returnFlight)    

https://stackoverflow.com/questions/65828793/need-a-sql-server-query-to-eliminate-the-highlighted-rows-returning-routes-in January 21, 2021 at 09:46PM