count trip in sql server -

my table structure

id zoneid status 1   35     in   starting  zone  2   35     out 1st trip has been started 3   36     in 4   36     in 5   36     out 6   38     in last station zone 1 trip completed  7   38     out returning 2nd trip has start 8   38     out 9   36     in 10  36     out 11  35     in when return in start zone means 2nd trip complete 12  35     in 13  35     in 14  35     out 3rd trip has been started  15  36     in 16  36     in 17  36     out 18  38     in 3rd trip has been completed 19  38     out 4th trip has been started 20  38     out 21  36     in 22  36     out 23  35     in 4th trip completed 24  35     in  

now want sql query, can count no of trips. not want use status field count edit

i want result total trips 35 starting point , 38 ending point(this 1 trip), when again 35 occures after 38 means 2 trip , on.

so don't want @ status, @ zoneid changes ordered id. zoneid 36 irrelevant, select 35 , 38 only, order them id , count changes. detect changes comparing record previous one. can previous record lag.

select sum(ischange) trips_completed (   select      case when zoneid <> lag(zoneid) on (order id) 1 else 0 end ischange   trips   zoneid in (35,38) ) changes_detected;