We have a table that will alternate records for arrivals and departures (simplifying, there are many more events that we would not care for this particular query) and what I need to accomplish is to put each arrival with its corresponding departure, ie:

Source Table:

Time Train Departure other fields
Time Train Arrival other fields
Time Train Departure other fields
...
...
Time Train Departure other fields
Time Train Arrival other fields

And I need:

Train, Departure Time, Arrival Time, departure other fields, arrival other fields.

So far so good, not issues here with this query:

SELECT			Dep.TimeStamp as Departed, 
			Arr.TimeStamp as Arrived, 
			Dep.LogId as DepartedLogID, 
			Arr.LogId as ArrivedLogID, 
			Dep.msgText as DepartedText,
			Arr.msgText as ArrivedText,
			Dep.Train,
			Dep.Veh1,
			Dep.Veh2,
			Dep.Veh3,
			Dep.tstc as DepartedTSTC,
			Arr.tstc as ArrivedTSTC,
			Arr.Dwell as Dwell,
			datediff(second, Dep.TimeStamp, Arr.TimeStamp) as Interval
	FROM	(
				SELECT		Dep.[pk] as fkDeparture,
						Arr.[pk] as fkArrival
				  FROM		[SMC].[dbo].[SmcLog] Dep (NOLOCK)
				  CROSS APPLY 
  						   (
								SELECT	 TOP 1 
										Arr.[PK] 
									FROM	[SMC].[dbo].[SmcLog] Arr (NOLOCK)
									Where	Arr.LogID = 292 and Arr.Train = Dep.Train and Arr.TimeStamp > Dep.TimeStamp -- Find arrival
									ORDER	BY Arr.TimeStamp ASC
					
						   ) Arr
				  WHERE		LogID = 324 and Dep.TimeStamp between @Start and @End -- Find departures
			) P
	JOIN	[SMC].[dbo].[SmcLog] Dep on Dep.PK = P.fkDeparture
	JOIN	[SMC].[dbo].[SmcLog] Arr on Arr.PK = P.fkArrival
    ORDER	by Dep.Train, Dep.TimeStamp desc

The important query is the inner one (P), where I just get the Primary keys of the table to get the rest of the fields I might want (using the Joins). This query works well, I get the data I need and it is instant for a day and it takes only a couple of seconds for a month (which we will never use, I just use a month to test the speed, as the table has 200 million records and it grows at around 200/400 thousand records per day.

The problem is that the beautiful pattern of departures and arrivals is not accurate, as sometimes it is possible two have two arrivals or two departures in a row. for the first case, no problemo, a simple fix on the inner query will solve the issue:

SELECT			Dep.TimeStamp as Departed, 
			Arr.TimeStamp as Arrived, 
			Dep.LogId as DepartedLogID, 
			Arr.LogId as ArrivedLogID, 
			Dep.msgText as DepartedText,
			Arr.msgText as ArrivedText,
			Dep.Train,
			Dep.Veh1,
			Dep.Veh2,
			Dep.Veh3,
			Dep.tstc as DepartedTSTC,
			Arr.tstc as ArrivedTSTC,
			Arr.Dwell as Dwell,
			datediff(second, Dep.TimeStamp, Arr.TimeStamp) as Interval,
			case when Arr.LogID = 292 then 1 else 0 end as isArrival
	FROM	(
				SELECT		[Dep].[pk] as fkDeparture,
						[Arr].[pk] as fkArrival
				  FROM		[SMC].[dbo].[SmcLog] Dep (NOLOCK)
				  CROSS APPLY 
  						   (
								SELECT TOP 1 
										[Arr].[PK] 
									FROM	[SMC].[dbo].[SmcLog] Arr (NOLOCK)
									Where	Arr.LogID IN (292, 324) and Arr.Train = Dep.Train and Arr.TimeStamp > Dep.TimeStamp -- Find arrival
									ORDER	BY Arr.TimeStamp ASC
					
						   ) Arr
				  WHERE		LogID = 324 and Dep.TimeStamp between @Start and @End -- Find departures
			) P
	JOIN	[SMC].[dbo].[SmcLog] Dep on Dep.PK = P.fkDeparture
	JOIN	[SMC].[dbo].[SmcLog] Arr on Arr.PK = P.fkArrival
    ORDER	by Dep.Train, Dep.TimeStamp desc

This query will make the second departure an arrival and fix the issue, and is still instantaneous, the problem is that I cannot think of an easy solution for the case where there are two consecutive departures, any ideas?

Summary:

First query handles D-A-D-A......D-A
Second query handles D-A-D-A-D1-D2-A....D-A by making D2 an Arrival in the CROSS APPLY, and keeping it for the next record as it is a departure (LogID 324)
The problem is D-A-D-A1-A2-D...D-A for I need to somehow insert a Departure between A1 and A2, or consider A2 as a Departure, but I can't think of how (cannot use fancy 2012/4 sql, this is 2008)


[Update]
My solution for now, which I do not like but it seems to work and is still instantaneous is to have an Union in the inner query to capture all the Arrival-Arrival pairs, and take the first one as a departure

				  UNION
				SELECT		[Dep].[pk] as fkDeparture,
							[Arr].[pk] as fkArrival
				  FROM		[SMC].[dbo].[SmcLog] Dep (NOLOCK)
				  CROSS APPLY 
  						   (
								SELECT		TOP 1 
											[Arr].[PK],
											Arr.LogId 
									FROM	[SMC].[dbo].[SmcLog] Arr (NOLOCK)
									Where	Arr.LogID IN (292, 324) and Arr.Train = Dep.Train and Arr.TimeStamp > Dep.TimeStamp -- Find arrival
									ORDER	BY Arr.TimeStamp ASC
					
						   ) Arr
				  WHERE		Dep.LogID = 292 and Arr.LogID = 292 and Dep.TimeStamp between @Start and @End -- Find two consecutive arrivals, then the first one is a departure.

I think this will do, to many other things to do to get stuck in aesthetics :)