0

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.

5
  • I would highly recommend going back and rethinking your table structure, because it's so unclear. Create a new column called tripid and have another table called trip. You should know what trip each bus is doing at certain times (e.g. a timetable). Having the tripid would solve all your problems – Mark Sinkinson Jan 29 '16 at 11:10
  • @MarkSinkinson, I appreciate the suggestion. However, the table mentioned here has many other columns and there are millions of records in the table. That is why maintaining other table would be tough. Besides this, I need to count the no of trips in a particular time interval. I managed to fetch other columns, but here I am stuck – A J Jan 29 '16 at 11:17
  • 1
    @Abhishek So you don't actually 'have a table structure like this'?Perhaps there are other columns in this table that can help you and help us to help you? – Mark Sinkinson Jan 29 '16 at 11:19
  • 1
    @MarkSinkinson The table has id, datetime, latitude, longitude, deviceid. Among these, id, datetime might be useful according to me. – A J Jan 29 '16 at 11:24
  • 2
    Then why did you add the IN/OUT column in the question and not the datetime one? – ypercubeᵀᴹ Jan 29 '16 at 11:27
3

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.

0
1

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;
0

Not the answer you're looking for? Browse other questions tagged or ask your own question.