SQL query determine stopped time in a range

I have to determine stopped time of an vehicle that sends back to server its status data every 30 second and this data is stored in a table of a database.

The 开发者_JAVA百科fields of a status record consist of (vehicleID, ReceiveDate, ReceiveTime, Speed, Location).

Now what I want to do is, determine each suspension time at the point that vehicle speed came to zero to the status the vehicle move again and so on for next suspension time. For example on a given day, a given vehicle may have 10 stopped status and I must determine duration of each by a query.

The result can be like this:

id Recvdate    Rtime  Duration   
1  2010-05-01  8:30   45min  
1  2110-05-01  12:21  3hour 

This is an application of windows functions (called analytic functions in Oracle).

Your goal is to assign a "block number" to each sequence of stops. That is, all stops in a sequence (for a vehicle) will have the same block number, and this will be different from all other sequences of stops.

Here is a way to assign the block number:

1. Create a speed flag that says 1 when speed > 0 and 0 when speed = 0.
2. Enumerate all the records where the speed flag = 1. These are "blocks".
3. Do a self join to put each flag = 0 in a block (this requires grouping and taking the max blocknum).
4. Summarize by duration or however you want.

The following code is a sketch of what I mean. It won't solve your problem, because you are not clear about how to handle day breaks, what information you want to summarize, and it has an off-by-1 error (in each sequence of stops it includes the previous non-stop, if any).

with vd as 
(
   select vd.*,
      (case when SpeedFlag = 1 
           then ROW_NUMBER() over (partition by id, SpeedFlag) end) as blocknum
   from 
   (
      select vd.*, (case when speed = 0 then 0 else 1 end) as SpeedFlag
      from vehicaldata vd
   ) vd
)
select id, blocknum, COUNT(*) as numrecs, SUM(duration) as duration
from 
(
   select vd.id, vd.rtime, vd.duration, MAX(vdprev.blocknum) as blocknum
   from vd 
   left outer join vd vdprev
     on vd.id = vdprev.id 
     and vd.rtime > vdprev.rtime
   group by vd.id, vd.rtime, vd.duration
) vd
group by id, blocknum