2
$\begingroup$

I work for a fire and EMS dispatch agency in Florida. All of our apparatus have gps to record the longitude, latitude, timestamp, apparatus, speed, unit status and the incident number. The klaxon in the fire house sounds and dispatches the ambulance to an emergency. The crew has 60 seconds to respond and this is called turnout time. The crew gets in the apparatus and starts driving to the scene. Wheels starting to roll is called en-route time. When the ambulance arrives at the emergency it is coded as Arrive on Scene. The gps is recording all of this and transmits it into a SQL Server 2012 database. My problem is this: Occasionally a crew will call on the radio and put themselves into en-route status while they are loitering at the fire house (using the bathroom, finishing their sandwich, waiting for a commercial break from their favorite show). At the dispatch center we think the ambulance is en-route - but the ambulance is actually standing still.

What I want to achieve: I want to skip the first n rows where the ambulance is loitering at the fire house. I have provided some data in this post. Skipping the first n rows where the ambulance is loitering allows me to calculate a true en-route time. It also allows me to monitor my system to make sure emergencies are being responded to quickly - or correct it before it becomes a problem.

NOTE: This query is not as simple as (WHERE Speed <> 0) and here is why; An ambulance goes en-route and arrives at the emergency and forgets to call in to Dispatch to tell them they've arrived on scene. The speed is registering as 0 but they have actually arrived on the scene, just forgot to call in to the dispatch center. All of these type of errors are on the back end of the incident. That is ok - at least the ambulance is at the scene of the emergency. I am looking to skip the first n observations at the front end of the incident where the speed is 0 but the crew claims to be en-route.

Geo-fencing is not an option. I am looking for pure Transact SQL 2012 method. I have tried to use OFFSET and FETCH but I can't figure it out. I have also tried to do some fancy stuff with the latitude and longitude to measure movement, but that doesn't solve my problem of the back-end when the ambulance is arrived on scene but is not moving.

In the sample data below my goal would be to skip the first 17 rows up until 06:51:04 which is when the ambulance starts to move at 6 miles per hour. I want that to be my starting row. Also note towards the end of the incident at 06:55:02 the ambulance appears to be on scene. I want to keep these rows because they do not pertain to loitering at the station. It simply appears that the crew forgot to call in "arrive on scene".

At the end of the day I want to skip first n rows because the speed is 0, I suspect the crew is likely to be goofing off at the fire house. But leave the last n rows where the speed is 0, I suspect the crew is arrived on scene and merely forgot to register Arrive on Scene.

This database is massive. I have more than 200 fire engines and 40 ambulances running 24 hours a day 7 days a week. My sample below is for just one emergency with a single ambulance. Just to get a start.

/There is not a primary key because this is just a flat table./ /My date format is yyyy-mm-dd hh:mm:ss.xxx/ CREATE TABLE AmbulanceGPS ( Incident VARCHAR(20) NULL , Ambulance VARCHAR(5) NULL , DateTimeStamp DATETIME NOT NULL , UnitStatus VARCHAR(10) NULL , Latitude FLOAT NULL , Longitude FLOAT NULL , Speed TINYINT NULL ) ; INSERT INTO AmbulanceGPS VALUES (MLC170314022391,LCM04,2017-03-14 06:49:29.410,EnRoute,26.56674,-81.95833,0) , (MLC170314022391,LCM04,2017-03-14 06:49:29.670,EnRoute,26.56674,-81.95833,0) , (MLC170314022391,LCM04,2017-03-14 06:49:29.680,EnRoute,26.56674,-81.95833,0) , (MLC170314022391,LCM04,2017-03-14 06:49:34.633,EnRoute,26.56674,-81.95833,0) , (MLC170314022391,LCM04,2017-03-14 06:49:39.520,EnRoute,26.56674,-81.95833,0) , (MLC170314022391,LCM04,2017-03-14 06:49:44.477,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:49:49.520,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:49:54.697,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:49:59.470,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:50:04.497,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:50:09.470,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:50:14.510,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:50:19.577,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:50:24.537,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:50:29.580,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:50:34.453,EnRoute,26.56674,-81.95832,0) , (MLC170314022391,LCM04,2017-03-14 06:50:39.603,EnRoute,26.56674,-81.95832,1) , (MLC170314022391,LCM04,2017-03-14 06:50:44.420,EnRoute,26.56685,-81.95834,6) , (MLC170314022391,LCM04,2017-03-14 06:50:49.437,EnRoute,26.56696,-81.95828,6) , (MLC170314022391,LCM04,2017-03-14 06:50:54.427,EnRoute,26.56694,-81.95827,0) , (MLC170314022391,LCM04,2017-03-14 06:50:59.450,EnRoute,26.56694,-81.95826,1) , (MLC170314022391,LCM04,2017-03-14 06:51:04.563,EnRoute,26.56694,-81.95811,6) , (MLC170314022391,LCM04,2017-03-14 06:51:09.510,EnRoute,26.56705,-81.95783,17) , (MLC170314022391,LCM04,2017-03-14 06:51:14.153,EnRoute,26.56755,-81.9576,33) , (MLC170314022391,LCM04,2017-03-14 06:51:17.183,EnRoute,26.56794,-81.95731,39) , (MLC170314022391,LCM04,2017-03-14 06:51:20.130,EnRoute,26.56829,-81.95692,42) , (MLC170314022391,LCM04,2017-03-14 06:51:23.230,EnRoute,26.56859,-81.95642,44) , (MLC170314022391,LCM04,2017-03-14 06:51:26.200,EnRoute,26.56877,-81.95584,46) , (MLC170314022391,LCM04,2017-03-14 06:51:29.167,EnRoute,26.56892,-81.95522,48) , (MLC170314022391,LCM04,2017-03-14 06:51:32.117,EnRoute,26.56908,-81.95457,50) , (MLC170314022391,LCM04,2017-03-14 06:51:35.097,EnRoute,26.56924,-81.95391,51) , (MLC170314022391,LCM04,2017-03-14 06:51:38.160,EnRoute,26.5694,-81.95324,51) , (MLC170314022391,LCM04,2017-03-14 06:51:41.270,EnRoute,26.56957,-81.95257,52) , (MLC170314022391,LCM04,2017-03-14 06:51:44.170,EnRoute,26.56976,-81.95191,52) , (MLC170314022391,LCM04,2017-03-14 06:51:47.203,EnRoute,26.57001,-81.95126,52) , (MLC170314022391,LCM04,2017-03-14 06:51:50.197,EnRoute,26.57029,-81.95063,52) , (MLC170314022391,LCM04,2017-03-14 06:51:53.300,EnRoute,26.57062,-81.95003,52) , (MLC170314022391,LCM04,2017-03-14 06:51:56.210,EnRoute,26.57098,-81.94946,52) , (MLC170314022391,LCM04,2017-03-14 06:51:59.153,EnRoute,26.5714,-81.9489,53) , (MLC170314022391,LCM04,2017-03-14 06:52:02.183,EnRoute,26.57185,-81.94837,54) , (MLC170314022391,LCM04,2017-03-14 06:52:05.220,EnRoute,26.57233,-81.94788,54) , (MLC170314022391,LCM04,2017-03-14 06:52:08.173,EnRoute,26.57283,-81.94743,53) , (MLC170314022391,LCM04,2017-03-14 06:52:11.180,EnRoute,26.57336,-81.94703,52) , (MLC170314022391,LCM04,2017-03-14 06:52:14.210,EnRoute,26.5739,-81.9467,52) , (MLC170314022391,LCM04,2017-03-14 06:52:17.133,EnRoute,26.57447,-81.94639,52) , (MLC170314022391,LCM04,2017-03-14 06:52:20.197,EnRoute,26.57505,-81.94614,50) , (MLC170314022391,LCM04,2017-03-14 06:52:23.133,EnRoute,26.57558,-81.94589,46) , (MLC170314022391,LCM04,2017-03-14 06:52:26.223,EnRoute,26.57603,-81.94552,45) , (MLC170314022391,LCM04,2017-03-14 06:52:29.110,EnRoute,26.57634,-81.94502,45) , (MLC170314022391,LCM04,2017-03-14 06:52:32.207,EnRoute,26.57646,-81.94443,44) , (MLC170314022391,LCM04,2017-03-14 06:52:35.150,EnRoute,26.57646,-81.94391,33) , (MLC170314022391,LCM04,2017-03-14 06:52:40.250,EnRoute,26.57645,-81.94359,7) , (MLC170314022391,LCM04,2017-03-14 06:52:45.087,EnRoute,26.57648,-81.94343,13) , (MLC170314022391,LCM04,2017-03-14 06:52:49.190,EnRoute,26.57685,-81.94319,28) , (MLC170314022391,LCM04,2017-03-14 06:52:53.117,EnRoute,26.57739,-81.94308,37) , (MLC170314022391,LCM04,2017-03-14 06:52:56.083,EnRoute,26.57788,-81.94296,42) , (MLC170314022391,LCM04,2017-03-14 06:52:58.950,EnRoute,26.57823,-81.94287,44) , (MLC170314022391,LCM04,2017-03-14 06:53:02.067,EnRoute,26.57899,-81.94271,48) , (MLC170314022391,LCM04,2017-03-14 06:53:05.140,EnRoute,26.57958,-81.94259,49) , (MLC170314022391,LCM04,2017-03-14 06:53:08.067,EnRoute,26.58017,-81.94248,50) , (MLC170314022391,LCM04,2017-03-14 06:53:11.083,EnRoute,26.58078,-81.94237,50) , (MLC170314022391,LCM04,2017-03-14 06:53:14.137,EnRoute,26.58139,-81.94228,51) , (MLC170314022391,LCM04,2017-03-14 06:53:17.140,EnRoute,26.58201,-81.94219,51) , (MLC170314022391,LCM04,2017-03-14 06:53:20.040,EnRoute,26.58263,-81.94212,52) , (MLC170314022391,LCM04,2017-03-14 06:53:23.090,EnRoute,26.58327,-81.94206,53) , (MLC170314022391,LCM04,2017-03-14 06:53:26.083,EnRoute,26.58392,-81.942,54) , (MLC170314022391,LCM04,2017-03-14 06:53:29.203,EnRoute,26.58458,-81.94197,55) , (MLC170314022391,LCM04,2017-03-14 06:53:31.100,EnRoute,26.58503,-81.94195,55) , (MLC170314022391,LCM04,2017-03-14 06:53:33.007,EnRoute,26.58548,-81.94195,56) , (MLC170314022391,LCM04,2017-03-14 06:53:35.110,EnRoute,26.58593,-81.94193,56) , (MLC170314022391,LCM04,2017-03-14 06:53:37.067,EnRoute,26.58639,-81.94191,56) , (MLC170314022391,LCM04,2017-03-14 06:53:39.090,EnRoute,26.58684,-81.94191,56) , (MLC170314022391,LCM04,2017-03-14 06:53:41.123,EnRoute,26.58728,-81.94191,55) , (MLC170314022391,LCM04,2017-03-14 06:53:44.073,EnRoute,26.5879,-81.94191,48) , (MLC170314022391,LCM04,2017-03-14 06:53:47.060,EnRoute,26.5884,-81.9419,36) , (MLC170314022391,LCM04,2017-03-14 06:53:52.130,EnRoute,26.58877,-81.94186,16) , (MLC170314022391,LCM04,2017-03-14 06:53:57.073,EnRoute,26.58888,-81.94146,23) , (MLC170314022391,LCM04,2017-03-14 06:54:01.183,EnRoute,26.58888,-81.94081,31) , (MLC170314022391,LCM04,2017-03-14 06:54:05.123,EnRoute,26.58888,-81.9402,35) , (MLC170314022391,LCM04,2017-03-14 06:54:09.100,EnRoute,26.58887,-81.93953,37) , (MLC170314022391,LCM04,2017-03-14 06:54:12.123,EnRoute,26.58887,-81.93901,38) , (MLC170314022391,LCM04,2017-03-14 06:54:15.103,EnRoute,26.58887,-81.93848,38) , (MLC170314022391,LCM04,2017-03-14 06:54:18.160,EnRoute,26.58887,-81.93797,40) , (MLC170314022391,LCM04,2017-03-14 06:54:22.160,EnRoute,26.58887,-81.93733,28) , (MLC170314022391,LCM04,2017-03-14 06:54:27.083,EnRoute,26.58904,-81.93703,15) , (MLC170314022391,LCM04,2017-03-14 06:54:32.120,EnRoute,26.58936,-81.93704,15) , (MLC170314022391,LCM04,2017-03-14 06:54:37.147,EnRoute,26.58965,-81.93704,13) , (MLC170314022391,LCM04,2017-03-14 06:54:42.133,EnRoute,26.58992,-81.93703,13) , (MLC170314022391,LCM04,2017-03-14 06:54:47.153,EnRoute,26.59019,-81.93704,13) , (MLC170314022391,LCM04,2017-03-14 06:54:52.107,EnRoute,26.59044,-81.93702,10) , (MLC170314022391,LCM04,2017-03-14 06:54:57.220,EnRoute,26.59062,-81.93701,7) , (MLC170314022391,LCM04,2017-03-14 06:55:02.180,EnRoute,26.59071,-81.93701,2) , (MLC170314022391,LCM04,2017-03-14 06:55:10.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:15.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:20.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:25.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:30.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:35.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:40.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:45.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:50.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:55:55.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:56:00.180,EnRoute,26.59071,-81.93701,0) , (MLC170314022391,LCM04,2017-03-14 06:56:10.180,EnRoute,26.59071,-81.93701,0) ;

$\endgroup$
2
$\begingroup$

In your problem description you want to extract all the rows after the truck starts moving. You state that you want all the rows after 06:51:04 where the speed has reached 6. However, the speed reached 6, twice before that. Without stating a more robust decision boundary this would not be an easy to solve problem. However, if I assume you want to detect the first presence of a speed greater than or equal to 5 then it is simple. You can play with this threshold speed value.

I would first find the DateTimeStamp for the first non-zero element in the recordlist and then get all rows that are greater than that.

Select * from AmbulanceGPS
where AmbulanceGPS.DateTimeStamp >= 
   (Select DateTimeStamp from AmbulanceGPS
    WHERE Speed >= 5 
    order by DateTimeStamp 
    LIMIT 1);
order by DateTimeStamp 

Furthermore, if you also want to reject all excess points in your calculation that are found once they have arrived at their location you can add this.

Select * from AmbulanceGPS
where AmbulanceGPS.DateTimeStamp >= 
   (Select DateTimeStamp from AmbulanceGPS
    WHERE Speed >= 5 
    order by DateTimeStamp 
    LIMIT 1)
and
AmbulanceGPS.DateTimeStamp <= 
   (Select DateTimeStamp from AmbulanceGPS
    WHERE Speed >= 5 
    order by DateTimeStamp DESC 
    LIMIT 1);

From here you can do last record of datetime minus the first record. And get the time it took for it to get to the emergency.

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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