BOR15K (4/24/2015)
...I have a list of flights - an itinerary with a request to find a final destination. The steps to find that final are as following:1. Find the longest route first (done that using ROW_NUMBER() )
2. check if previous or next segments have longer waiting time AND the mileage to that destination from the origin divided by longest mileage is no less than 75%
if yes - select the one with the longest waiting time.
...
I'm sorry but I don't see any relationship between the requirement - identifying the final destination - and the suggested steps. It's nonsense. You should be using a standardised datetime and the origin and destination of each leg compared to the others in the itinerary set. You will have to account for stopovers, legs travelled by means other than air, and itineraries which have repeats of the same leg, e.g.
BOM -> DEL
DEL -> PVG
PVG -> DEL
DEL-> PVG
PVG -> HSN
You cannot determine the final destination using waiting time and distance from origin alone. It won't work.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden