Skip to content
Advertisement

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)

std_utc = Departure Time Sta_utc = Arival Time

enter image description here

This query is kept on running and not giving me the desired output. Note: I am using Bigquery

Advertisement

Answer

I have created a table named S65828793 with your provided data. First I have numbered the rows in ascending sequence of departure time. Then from that I have identified the flights that have been another flight within two consecutive days from opposite direction and marked those as returning flight. Then at last I have excluded all the returning flight from the total flight list.

Result: enter image description here

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement