我在佛罗里达州的一家消防和 EMS 调度机构工作。我们所有的仪器都有GPS记录经度、纬度、时间戳、仪器、速度、单位状态和事件编号。消防站的喇叭响起，将救护车送往紧急情况。工作人员有 60 秒的时间做出反应，这称为投票时间。工作人员进入设备并开始开车前往现场。车轮开始滚动的时间称为航路时间。当救护车到达紧急情况时，它被编码为到达现场。gps 正在记录所有这些并将其传输到 SQL Server 2012 数据库中。我的问题是这样的：有时工作人员会在他们在消防站闲逛（使用浴室，吃完三明治，等待他们最喜欢的节目的广告时间）时打开收音机并让自己进入途中状态。

我想要实现的目标：我想跳过救护车在消防站徘徊的前 n 行。我在这篇文章中提供了一些数据。跳过救护车徘徊的前 n 行，我可以计算出真正的途中时间。它还允许我监控我的系统，以确保快速响应紧急情况 - 或者在它成为问题之前纠正它。

注意：这个查询不像（WHERE Speed <> 0）那么简单，这就是原因；一辆救护车在途中到达紧急情况，却忘记打电话给 Dispatch 告诉他们他们已经到达现场。速度注册为0，但他们实际上已经到达现场，只是忘记打电话给调度中心。所有这些类型的错误都在事件的后端。没关系 - 至少救护车在紧急情况现场。我希望跳过事件前端的前 n 个观察，其中速度为 0，但机组人员声称正在途中。

地理围栏不是一种选择。我正在寻找纯 Transact SQL 2012 方法。我曾尝试使用 OFFSET 和 FETCH 但我无法弄清楚。我也尝试用纬度和经度做一些花哨的东西来测量运动，但这并不能解决我在救护车到达现场但没有移动时后端的问题。

在下面的示例数据中，我的目标是跳过前 17 行，直到 06:51:04 救护车开始以每小时 6 英里的速度行驶。我希望那是我的起始行。另请注意，在 06:55:02 事件即将结束时，救护车似乎已到达现场。我想保留这些行，因为它们与在车站闲逛无关。看来工作人员忘了叫“到达现场”。

在一天结束时，我想跳过前 n 行，因为速度为 0，我怀疑工作人员可能会在消防站偷懒。但是离开速度为0的最后n行，我怀疑工作人员已经到达现场，只是忘记注册到达现场。

这个数据库很大。我有 200 多辆消防车和 40 辆救护车每周 7 天、每天 24 小时运行。我下面的示例仅适用于一辆救护车的紧急情况。只是为了开始。

/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) ;