... that is the question.
I'm working with some flight data at a SQL Server 2008R2 client, and specifically I have a requirement to come up with the ultimate origin (OrigApt) and destination (DestApt) for a given flight.
But some flights have one or more connections, such as in the below image, where my requirement is to come up with one row where OriginApt = YKM and DestApt=SLC, ignoring all of the connecting flights (i.e. SEA and BOI), which I'll define for now as any leg where the GapInMinutes (i.e. time between landing and next flight taking off) is less than 240 minutes.
So .. Does anyone have any ideas on how to pull this off other than a double cursor, with the outer cursor being the PK for the overall flight (not shown in the image), the inner cursor being each leg in the image, and incrimenting a number whenever there is a GapInMinutes > 240?
Thanks in advance.