Get start time and end time of a trip in SQL Server
My idea for this is ,first, to filter only the records with zoneID  35 or 38 that have have status = OUT  (this mean the start of the trip)

Then base on this, give me the next record(most near the the record as datetime)that have different zoneID and different status 
(Ex:If I have 35 and OUT , give me the next 38 and IN.)

And I added a additional condition.The previous zoneID (LAG) of the current zoneID are different 

DECLARE @Table1 TABLE
    ([id] int, [zoneid] int, [status] varchar(3), [datetime] datetime);

INSERT INTO @Table1
    ([id], [zoneid], [status], [datetime])
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');


SELECT
    [id]
    ,starttime
    ,endtime
FROM
(
    SELECT
        T1.[id]
        ,T1.[zoneid]
        ,T1.[status]
        ,T1.[datetime] AS starttime         
        ,CA.[datetime] AS endtime
        ,LAG(T1.[zoneid],1,1) OVER(ORDER BY T1.[id]) AS PrevZoneID
    FROM
        @Table1 AS T1
        CROSS APPLY
        (
            SELECT TOP(1)
                T2.[id], T2.[zoneid], T2.[status], T2.[datetime]
            FROM
                @Table1 AS T2
            WHERE       
                T1.[zoneid] &lt;&gt; T2.[zoneid] --give the other zone
                AND T1.[status] &lt;&gt; T2.[status] -- different status
                AND (T2.[zoneid] = 35 OR T2.[zoneid] = 38)
                AND T1.[datetime] &lt; T2.[datetime]
            ORDER BY 
                T2.[datetime] ASC    
        )CA
    WHERE
        (T1.[zoneid] = 35 AND T1.[status] = 'OUT')
        OR (T1.[zoneid] = 38 AND T1.[status] = 'OUT')
)A
WHERE
    A.PrevZoneID &lt;&gt; A.[zoneid];


I have the next output:

id          starttime               endtime    
2           2016-02-02 11:36:59.000 2016-02-02 11:39:37.000
7           2016-02-02 11:39:55.000 2016-02-02 11:42:01.000
14          2016-02-02 11:42:39.000 2016-02-02 11:44:00.000
19          2016-02-02 11:44:18.000 2016-02-02 11:46:00.000