How to make a sql loop query to check difference between values?

Question

first of all, I'm not a native english speaker and this is a hard to explain issue. If you have any doubts, please let me know.

We're using a GPS vehicle tracking device, which is programmed to send and store in a DB the position of the vehicle every 5 min when not moving, and every 100 meters when moving.

This DB has a table called "vehicle_gps" that stores the data, with values such as speed, position, datetime, vehicle_gps_id.

I need some kind of query that will show the different positions where the vehicle stopped for more than a certain amount of time(e.g 2 minutes, discarding traffic lights).

For instance, I need something that can tell me the following: "The vehicle was on Positon1 (P1) at 8:00, it left P1 towards P2, reaching it at 8.20. The vehicle stayed at P2 until 10.20 and it reached P3 at 10.50"

example of the records in the table

vehicle_gps_id  | datetime---------- | latitude | longitude | speed

1000------------| 05/16/2012 08:00:00|50.0000   |50.00000   |40 (km/h)
1001------------| 05/16/2012 08:01:00|51.0000   |51.00000   |38 (km/h)
1002------------| 05/16/2012 08:01:23|51.0045   |50.000054  |40 (km/h)
1003------------| 05/16/2012 08:01:40|51.00540  |51.0005430 |39 (km/h)
.
.
.
1040------------| 05/16/2012 08:20:40|53.00540  |53.0005430 |0 (km/h)
1041------------| 05/16/2012 08:25:40|53.00540  |53.0005430 |0 (km/h)
1042------------| 05/16/2012 08:30:40|53.00540  |53.0005430 |0 (km/h)
.
.
.
1060------------| 05/16/2012 10:20:40|53.00540|53.0005430|20 (km/h)

How can I do something like that?

So far, I was able to get the difference in minutes between fixed positions and for that reason, I wanted a loop that would check all dates between positions and break whenever that difference was longer than 5 minutes, which means that the vehicle had stopped.

TIA

jeschafe · Accepted Answer · 2012-05-17T15:21:30.300

0

DECLARE @idFrom as int,
        @idTo as int,
        @gpsDateFrom as datetime,
        @gpsDateTo as datetime
DECLARE VehicleCursor CURSOR FAST_FORWARD FOR 
SELECT  vehicle_gps_id, 
        datetimeCol
FROM    yourtable
ORDER BY vehicle_gps_id
OPEN VehicleCursor FETCH NEXT FROM VehicleCursor INTO @idFrom, @gpsDateFrom
    FETCH NEXT FROM VehicleCursor INTO @idTo, @gpsDateTo
    WHILE @@FETCH_STATUS = 0 BEGIN 
        IF DATEDIFF(MI,@gpsDateFrom,@gpsDateTo) >5
        BEGIN
            --Break (your code here)
        END
        SET @idFrom = @idTo
        SET @gpsDateFrom = @gpsDateTo
        FETCH NEXT FROM VehicleCursor INTO @idTo, @gpsDateTo
    END 
CLOSE VehicleCursor 
DEALLOCATE VehicleCursor

Something like this should work for you. It is a cursor that just runs through all your columns comparing datetimes. You can enter in whatever you want to do into the commented section after the if statement.

edited May 17 '12 at 15:21

answered May 16 '12 at 22:05

jeschafe

2,683
1
14
13

Thanks for your reply. I get 2 errors : 1)"Incorrect syntax near the keyword SET", right below the commented section. 2) "Incorrect syntax near VehicleCursor", at the end. – Heinser Diaz May 17 '12 at 00:40
Ah I forgot to change a variable name, dunno if it'll fix both problems tho, i've edited it so you can try again. – jeschafe May 17 '12 at 15:21
I didn't modify the commented section, hence the error message =P. On another hand, I still don't know how to display the information I need on every break. Just to test that it was working I did a select query, therefore it gives me 8 select query results, which is correct because that's the number of stops the vehicle made. Anyways, thanks again for your help – Heinser Diaz May 17 '12 at 16:31

How to make a sql loop query to check difference between values?

1 Answers1

Linked