Get no. of trips in SQL Server [closed]

Question

I have a table structure like this.

id zoneid status datetime
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

This table contains information about a bus that starts from zone 35 and ends at zone 38(That is 1 trip). After that it starts from 38 and ends at 35(That is 2 trip), and this goes on. So, I need a query to count total number of trips (according to data, it is 4). Any suggestions? Thanks.

Note: The status column cannot be taken into account while querying.

Answer 1

If the table data means that the datetimes of the trips are consistent with the id order, and trips always start at 35 and end at 38 and vice versa, then you could use the LAG() function, assuming that you are in SQL Server 2012 or later version:

with ins_and_outs as
  ( select zoneid, 
           previous_zoneid = lag(zoneid) over (order by id)
    from tablex
  ) 
select whole_trips = count(*) - 1
from ins_and_outs
where zoneid in (35, 38)
  and (previous_zoneid <> zoneid or previous_zoneid is null) ;

This checks the "previous" zoneid, based on id ordering to see if the zone has changed. You may have additional columns (with date/time data) that would be more accurate to base the order than the (order by id) I used.

Perhaps this variation might be more close to the description of a "trip" (from 35 to 38 and from 38 to 35):

with ins_and_outs as
  ( select zoneid, 
           previous_zoneid = lag(zoneid) over (order by id)
    where zoneid in (35, 38)
    from tablex
  ) 
select whole_trips = count(*) - 1
from ins_and_outs
where (previous_zoneid <> zoneid or previous_zoneid is null) ;

This will give different results, if there are cases, "trips" that start from zone 35, go to other zones and then return to 35 without going through zone 38. Query 1 will count such a trip. Query 2 will not count it.

Answer 2

Here is another solution:

select sum(countTrips) as TotalTrips
    from
    (
      select 
      case when zoneid <> lag(zoneid) over (order by id) then 1 else 0 end as countTrips
      from trips
      where zoneid in (35,38)
    ) TripChanged;