Here's a version that accepts any number of trip/route definitions and pulls all the answers at once. Aside from the join to the routes which should perform well with the right indexes, all the trip start and stop calculations are done without joins to a physical table (only using aggregates or constant scan joins). It additionally shows the time waiting at the station (or whatever you want to call waiting between trips).
WITH BusRoutes AS ( -- The bus routes or trips you want, easily defined
SELECT *
FROM (VALUES
('Line 1', 'Northbound', 35, 38),
('Line 1', 'Southbound', 38, 35)
) t (Line, Direction, FromZoneId, ToZoneId)
), Groups AS (
SELECT
br.FromZoneId,
br.ToZoneId,
Grp =
Row_Number() OVER (ORDER BY bzs.StatusDateTime)
- Row_Number() OVER (PARTITION BY br.FromZoneId, br.ToZoneId, bzs.ZoneId
ORDER BY bzs.StatusDateTime),
bzs.*
FROM
BusRoutes br
CROSS APPLY (VALUES
('IN', br.ToZoneId),
('OUT', br.FromZoneId)
) s ([Status], ZoneId)
INNER JOIN BusZoneStatuses bzs
ON s.ZoneId = bzs.ZoneId
AND s.[Status] = bzs.[Status]
), Ranges AS (
SELECT
g.FromZoneId,
g.ToZoneId,
TripId = Row_Number() OVER (PARTITION BY g.FromZoneId, g.ToZoneId
ORDER BY Min(g.StatusDateTime), X.Num) / 2,
StatusDateTime = Min(g.StatusDateTime),
g.[Status],
X.Num
FROM
Groups g
CROSS JOIN (VALUES (1), (2)) x (Num)
GROUP BY
g.FromZoneId,
g.ToZoneId,
g.[Status],
g.Grp,
X.Num
), Result AS (
SELECT
FromZoneId,
ToZoneId,
FromTime = Min(r.StatusDateTime),
ToTime = CASE WHEN Count(*) = 1 THEN NULL ELSE Max(r.StatusDateTime) END,
[Status] = Coalesce(
Min(CASE WHEN r.Num = 2 THEN r.[Status] ELSE NULL END),
Min(r.[Status])
)
FROM Ranges r
WHERE TripId > 0
GROUP BY
FromZoneId,
ToZoneId,
TripId
)
SELECT
br.Line,
br.Direction,
FromZoneId = CASE WHEN a.[Status] = 'IN' THEN NULL ELSE br.FromZoneId END,
br.ToZoneId,
a.Activity,
r.FromTime,
r.ToTime
FROM
BusRoutes br
INNER JOIN Result r
ON br.FromZoneId = r.FromZoneId
AND br.ToZoneId = r.ToZoneId
INNER JOIN (VALUES
('IN', 'At station'),
('OUT', 'On trip')
) a ([Status], Activity)
ON r.[Status] = a.[Status]
ORDER BY
br.Line,
a.Activity,
r.FromTime
;
The output:
Line Direction FromZone ToZone Activity FromTime ToTime
------ ---------- -------- ------ -------- ----------------------- -----------------------
Line 1 Southbound NULL 35 Station 2016-02-02 11:36:39.000 2016-02-02 11:39:55.000
Line 1 Northbound NULL 38 Station 2016-02-02 11:39:37.000 2016-02-02 11:42:39.000
Line 1 Southbound NULL 35 Station 2016-02-02 11:42:01.000 2016-02-02 11:44:18.000
Line 1 Northbound NULL 38 Station 2016-02-02 11:44:00.000 NULL
Line 1 Southbound NULL 35 Station 2016-02-02 11:46:00.000 NULL
Line 1 Northbound 35 38 On trip 2016-02-02 11:36:59.000 2016-02-02 11:39:37.000
Line 1 Southbound 38 35 On trip 2016-02-02 11:39:55.000 2016-02-02 11:42:01.000
Line 1 Northbound 35 38 On trip 2016-02-02 11:42:39.000 2016-02-02 11:44:00.000
Line 1 Southbound 38 35 On trip 2016-02-02 11:44:18.000 2016-02-02 11:46:00.000
Here's the setup data I used, in case anyone wants to play with it.
CREATE TABLE dbo.BusZoneStatuses (
Id int NOT NULL,
ZoneId int NOT NULL,
[Status] varchar(3) NOT NULL,
StatusDateTime datetime NOT NULL
);
INSERT dbo.BusZoneStatuses (Id, ZoneId, Status, StatusDateTime)
VALUES
(1, 35, 'IN', '2016-02-02 11:36:39'),
(2, 35, 'OUT', '2016-02-02 11:36:59'),
(3, 36, 'IN', '2016-02-02 11:37:35'),
(4, 36, 'IN', '2016-02-02 11:37:49'),
(5, 36, 'OUT', '2016-02-02 11:38:06'),
(6, 38, 'IN', '2016-02-02 11:39:37'),
(7, 38, 'OUT', '2016-02-02 11:39:55'),
(8, 38, 'OUT', '2016-02-02 11:40:09'),
(9, 36, 'IN', '2016-02-02 11:41:39'),
(10, 36, 'OUT', '2016-02-02 11:41:49'),
(11, 35, 'IN', '2016-02-02 11:42:01'),
(12, 35, 'IN', '2016-02-02 11:42:13'),
(13, 35, 'IN', '2016-02-02 11:42:28'),
(14, 35, 'OUT', '2016-02-02 11:42:39'),
(15, 36, 'IN', '2016-02-02 11:42:49'),
(16, 36, 'IN', '2016-02-02 11:43:03'),
(17, 36, 'OUT', '2016-02-02 11:43:19'),
(18, 38, 'IN', '2016-02-02 11:44:00'),
(19, 38, 'OUT', '2016-02-02 11:44:18'),
(20, 38, 'OUT', '2016-02-02 11:44:39'),
(21, 36, 'IN', '2016-02-02 11:45:20'),
(22, 36, 'OUT', '2016-02-02 11:45:43'),
(23, 35, 'IN', '2016-02-02 11:46:00'),
(24, 35, 'IN', '2016-02-02 11:46:04')
;
Anyone who would like an explanation for what's going on here can find a detailed explanation of the technique at this similar question/answer on StackOverflow.