I've spent an hour playing with this. This would work, if there was a maximum of one connection:

select t1.[pax id], t1.board, case when t2.[off] is null then '' else t1.[off] end as connecting, coalesce(t2.[off], t1.[off]) as [off], coalesce(t2.[flight date], t1.[flight date]) as [fake arrival date], abs(datediff(dd, t1.[flight date], t2.[flight date]))

from

table1 t1

left join table1 t2 on t1.[off] = t2.board and t1.[pax id] = t2.[pax id]

As there's many, you have several issues. The main one is simply that this cannot work, all the time. The database is fundamentally broken, and needs to be fixed for a workable solution to exist. The SQL you were given has a number of it's own issues, not least that it will ignore fellow passengers who have the same first name, which is far from impossible.

You can probably do this, by writing a cursor based solution. That's always slow and hideous, but the fact is, you need a ton of procedural code to try to work out what you're trying to work out, based on the non existent relevant data that you have. I asked you for the logic by which you hope to work out what constitutes a single trip, and you've not replied. I cannot think of anything, based on this data, that would reliably work, all the time. What if I am doing a round the world trip, and staying in each destination for a day ? There won't be any logical way to work out, without arrival times, how long I am staying, and if I am connecting. With arrival times, and I mean times, not just dates, for arrival and departure, then you could say 'anything greater than 8 hours is an end of trip', or 'any place I stay overnight, is the end of a trip'. That you need to fudge this basic information is another clue that this database design is utterly broken.

You need arrival dates as a minimum. That an airline would store departure dates without times, is unbelievable to me. The database needs to be normalised. There should be a table that stores a single trip, with connecting tables that store each step of the trip, and that store each passenger that's taking the trip.

If I was working for someone who gave me this, I'd quote on rewriting the entire system or tell them to find someone else. Life is too short to spend fixing other people's idiocy.