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

by


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.

with AllFlight as (
select row_number()over(order by departuretime)rn, aoc  ,hub    ,flight ,departure  ,arrival    ,DepartureTime  ,ArrivalTime from S65828793  
),
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 datediff(day,cast(s.ArrivalTime as date),cast(s2.departuretime as date))<=1))
select aoc  ,hub    ,flight ,departure  ,arrival    ,DepartureTime  ,ArrivalTime from AllFlight where rn not in (select rn from returnFlight)

Result:
enter image description here

1

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