August 29, 2018 at 8:44 pm
We have GPS data that we receive from several devices that we have. When we receive the data there is always a START marker in the data (messagecode=10), but not always and end marker (messagecode=20), since the devices are just powered off and not turned off which would generate an END MARKER. Therefore, we need to use alternate means to determine the end of one track and the start of another.
Attached is a extract of the data. The flight number column in the report is not in the actual data but it shows you how I want the data grouped or at least to have the flight number added during the select query. I could then filter only the LAST flight which is what I am after.
The way I would like to group these is as follows:
Start of Flight: messagecode=10
End of Flight: {see options below}
[option 1] - The last record before the next message code 10
**[option 2] - The row where the calculated difference between [timestamp] fields in the current row and the next (lead) row is > 30 mins.
** = <preferred method>
I know there are a lot of new functions in SQL 2017. I was hoping someone would be kind enough to help me figure out how to structure this SQL query. Speed of the query is important so cursors are out of the question (which based on my reading no one here would suggest in the first place), this is feeding our own home grown aircraft tracking application (not commercial and never will be).
Thank you in advance.
August 29, 2018 at 10:21 pm
From
(
Select * ,
Case
When MessageCode = 10 Then 1
When DateDiff(Minute, LAG(utc_datetime,1) Over (Order By utc_datetime), utc_datetime) > 30 Then 1
Else 0
End As NewStartPoint
From flights
) F
August 30, 2018 at 8:51 am
andycadley - Wednesday, August 29, 2018 10:21 PMYou're more likely to get help if you post data and expected results as consumable SQL DDL/DML rather than an Excel sheet which needs work to be able to try things out on. That said, I think something like the following will do what you want in principle:
Select *,sum(NewStartPoint) Over (Order By utc_datetime) as FlightNumber
From
(
Select * ,
Case
When MessageCode = 10 Then 1
When DateDiff(Minute, LAG(utc_datetime,1) Over (Order By utc_datetime), utc_datetime) > 30 Then 1
Else 0
End As NewStartPoint
From flights
) F
You should ALWAYS specify the window frame if it's relevant. The reason for this is that ROWS is much more efficient than RANGE, because ROWS only needs to know the values for the current and previous row, whereas RANGE needs to evaluate all tied rows. However, RANGE is the default, because it is deterministic, whereas ROWS isn't (always).
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 3, 2018 at 7:24 am
Hello,
Does this help, use the Lead function to see if the next row is a start row, then declare the actual row the end row.
If there is not next row, then assume that that would be a start row.
(A slight misunderstanding of the question resulted in the solution below. I thought the flight number was a given and significant for the grouping.
But the partitioning by the flightnumber can be replaced with a constant (partition by 1), and then there is a row 20 for each flight).
SELECT
CASE WHEN Lead(messagecode, 1, 10) OVER (partition by [Flight_Number] order by time) = 10
THEN 20
ELSE messagecode END new_message_code,
CASE WHEN DateDiff(Minute, time, Lead(time,1,'20991231') Over (Order By time)) > 30
THEN 20
ELSE messagecode END Preferred_new_message_code,
* FROM GPSLog
This makes each row before a start an end row and the last row is also an endrow.
Only problem is if you have Two startrows without any row in between.
**) Although the given example gives plenty rows, it does not give enough variation on different situations.
Ben
The complete example and solution.
(I leave the insertion of the Flight_number to the reader).
-- exec sp_drop GPSLog Drops the GPSLog table when it exists.
DROP TABLE IF Exits GPSLog
Create table GPSLog(
messagecode varchar(300)
,messagecodelabel varchar(300)
,alt varchar(300)
,lat varchar(300)
,lon varchar(300)
,speed varchar(300)
,azimuth varchar(300)
,time datetime
,Flight_Number int)
-- exec sp_generate_inserts GPSlog
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('10','Start Track','183.8492','33.94970655','-117.4410796','1','337.5','Aug 29 2018 3:09:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','185.9','33.95161629','-117.4408436','3.003','22.5','Aug 29 2018 3:11:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94987822','-117.4412727','0','0','Aug 29 2018 3:13:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94987822','-117.4412727','0','0','Aug 29 2018 3:15:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94988894','-117.4412727','0','0','Aug 29 2018 3:17:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94989967','-117.4412727','0','0','Aug 29 2018 3:19:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94989967','-117.4412942','0','0','Aug 29 2018 3:21:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94989967','-117.4412942','0','0','Aug 29 2018 3:23:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94989967','-117.4412942','0','0','Aug 29 2018 3:25:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94989967','-117.4412942','0','0','Aug 29 2018 3:27:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','229.06','33.94993186','-117.4413157','0','0','Aug 29 2018 3:29:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','231.12','33.94992113','-117.4413157','0','0','Aug 29 2018 3:31:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','231.12','33.94992113','-117.4412942','0','0','Aug 29 2018 3:33:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','231.12','33.94992113','-117.4412942','0','0','Aug 29 2018 3:35:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','231.12','33.9499104','-117.4412942','0','0','Aug 29 2018 3:37:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','231.12','33.94989967','-117.4412942','0','0','Aug 29 2018 3:39:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','231.12','33.94988894','-117.4412942','0','0','Aug 29 2018 3:41:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','233.18','33.94988894','-117.4412942','0','0','Aug 29 2018 3:43:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','255.85','33.94985676','-117.4413371','0','0','Aug 29 2018 3:45:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','253.79','33.94986749','-117.4413586','2.001','112.5','Aug 29 2018 3:47:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','253.79','33.94985676','-117.4413586','0','0','Aug 29 2018 3:49:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','255.85','33.94985676','-117.4413586','0','0','Aug 29 2018 3:51:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','253.79','33.94984603','-117.44138','0','0','Aug 29 2018 3:53:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','253.79','33.94984603','-117.44138','0','0','Aug 29 2018 3:55:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','251.73','33.94984603','-117.4413586','0','0','Aug 29 2018 3:57:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','249.67','33.94984603','-117.4413371','0','0','Aug 29 2018 3:59:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','247.61','33.94984603','-117.4413371','0','0','Aug 29 2018 4:01:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','247.61','33.94985676','-117.4413371','0','0','Aug 29 2018 4:03:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','245.54','33.94985676','-117.4413371','0','0','Aug 29 2018 4:05:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','245.54','33.94985676','-117.4413371','0','0','Aug 29 2018 4:07:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','245.54','33.94985676','-117.4413586','0','0','Aug 29 2018 4:09:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','245.54','33.94986749','-117.4413586','0','0','Aug 29 2018 4:11:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','245.54','33.94986749','-117.4413586','0','0','Aug 29 2018 4:13:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','243.48','33.94986749','-117.4413586','0','0','Aug 29 2018 4:15:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','245.54','33.94985676','-117.4413371','0','0','Aug 29 2018 4:17:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','245.54','33.94985676','-117.4413371','0','0','Aug 29 2018 4:19:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','243.48','33.94986749','-117.4413371','0','0','Aug 29 2018 4:21:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','243.48','33.94988894','-117.4413586','0','0','Aug 29 2018 4:23:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','233.18','33.94994259','-117.4414444','2.001','315','Aug 29 2018 4:25:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','235.24','33.94996405','-117.4414873','0','0','Aug 29 2018 4:27:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','235.24','33.94988894','-117.4413586','0','0','Aug 29 2018 4:29:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','235.24','33.94986749','-117.4413371','0','0','Aug 29 2018 4:31:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','237.3','33.94985676','-117.4413371','0','0','Aug 29 2018 4:33:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','237.3','33.94985676','-117.4413157','0','0','Aug 29 2018 4:35:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','237.3','33.94985676','-117.4413371','0','0','Aug 29 2018 4:37:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','237.3','33.94984603','-117.4413157','0','0','Aug 29 2018 4:39:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','237.3','33.94985676','-117.4413371','0','0','Aug 29 2018 4:41:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','237.3','33.94984603','-117.4413371','0','0','Aug 29 2018 4:43:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','237.3','33.94985676','-117.4413371','0','0','Aug 29 2018 4:45:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','237.3','33.94985676','-117.4413371','0','0','Aug 29 2018 4:47:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('10','Start Track','206.437256','33.95028591','-117.4417448','0','112.5','Aug 29 2018 5:41:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','249.67','33.95074725','-117.4397922','17.474','112.5','Aug 29 2018 5:43:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','255.85','33.9497602','-117.4347711','0','0','Aug 29 2018 5:45:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','255.85','33.9497602','-117.4347711','0','0','Aug 29 2018 5:47:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','255.85','33.94974947','-117.4347711','0','0','Aug 29 2018 5:49:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','255.85','33.94974947','-117.4347711','0','0','Aug 29 2018 5:51:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','259.98','33.9499104','-117.4349427','0','0','Aug 29 2018 5:53:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','259.98','33.9499104','-117.4349427','0','0','Aug 29 2018 5:53:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','453.49','33.96684051','-117.4573445','188.762','22.5','Aug 29 2018 5:55:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','996.04','33.97881389','-117.4025416','174.096','90','Aug 29 2018 5:57:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','1357.62','33.98103476','-117.336967','192.569','90','Aug 29 2018 5:59:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','1655.04','34.0310204','-117.3367524','185.012','337.5','Aug 29 2018 6:01:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','1940.68','34.08626318','-117.3567295','192.569','337.5','Aug 29 2018 6:03:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','2180.67','34.14411306','-117.3764277','212.475','337.5','Aug 29 2018 6:05:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','2440.91','34.20374393','-117.3949885','212.475','337.5','Aug 29 2018 6:07:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','2663.16','34.26455498','-117.4190855','216.634','337.5','Aug 29 2018 6:09:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','2878.38','34.33067679','-117.4299645','233.884','0','Aug 29 2018 6:11:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3100.79','34.36926842','-117.4798965','200.356','292.5','Aug 29 2018 6:13:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3255.32','34.39366579','-117.5484753','196.434','292.5','Aug 29 2018 6:15:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3255.32','34.39366579','-117.5484753','196.434','292.5','Aug 29 2018 6:15:30:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3455.12','34.42193627','-117.6134706','216.634','292.5','Aug 29 2018 6:17:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3455.12','34.42193627','-117.6134706','216.634','292.5','Aug 29 2018 6:17:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3461.56','34.4726193','-117.6470947','297.109','67.5','Aug 29 2018 6:19:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3461.56','34.4726193','-117.6470947','297.109','67.5','Aug 29 2018 6:19:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3343.41','34.4775331','-117.6624155','302.45','22.5','Aug 29 2018 6:21:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3343.41','34.4775331','-117.6624155','302.45','22.5','Aug 29 2018 6:21:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3384.69','34.46839213','-117.6002312','212.475','180','Aug 29 2018 6:23:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3337.08','34.40855742','-117.5811553','225.135','157.5','Aug 29 2018 6:25:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3314.97','34.41338539','-117.556951','163.669','270','Aug 29 2018 6:27:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3349.75','34.41776276','-117.6143718','185.012','202.5','Aug 29 2018 6:29:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3337.08','34.36396837','-117.5788164','238.353','112.5','Aug 29 2018 6:31:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3346.58','34.41279531','-117.5433898','208.375','0','Aug 29 2018 6:33:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3352.92','34.44022894','-117.5890517','150.502','247.5','Aug 29 2018 6:35:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3349.75','34.39354777','-117.6141143','196.434','180','Aug 29 2018 6:37:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.380759','-117.5505996','238.353','67.5','Aug 29 2018 6:39:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.43791151','-117.5428963','192.569','315','Aug 29 2018 6:41:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3352.92','34.43667769','-117.598772','181.318','202.5','Aug 29 2018 6:43:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3365.61','34.38098431','-117.6003385','208.375','157.5','Aug 29 2018 6:45:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.37772274','-117.5304508','242.885','45','Aug 29 2018 6:47:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3356.09','34.43776131','-117.5296354','200.356','315','Aug 29 2018 6:49:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3352.92','34.46826339','-117.5874424','174.096','270','Aug 29 2018 6:51:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.43022966','-117.6270318','188.762','202.5','Aug 29 2018 6:53:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3362.43','34.37271237','-117.6149082','192.569','157.5','Aug 29 2018 6:55:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3352.92','34.35853958','-117.5469518','242.885','67.5','Aug 29 2018 6:57:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3356.09','34.41722631','-117.5150657','204.336','337.5','Aug 29 2018 6:59:45:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3356.09','34.43958521','-117.5680232','170.567','270','Aug 29 2018 7:02:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3356.09','34.42412496','-117.6261306','177.68','225','Aug 29 2018 7:04:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.36934352','-117.6277399','208.375','135','Aug 29 2018 7:06:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.35680151','-117.5523591','247.482','67.5','Aug 29 2018 7:08:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.41537023','-117.5176835','208.375','337.5','Aug 29 2018 7:10:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3356.09','34.44737434','-117.566328','167.092','270','Aug 29 2018 7:12:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3349.75','34.42816973','-117.6231265','170.567','225','Aug 29 2018 7:14:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3356.09','34.45028186','-117.6742172','177.68','292.5','Aug 29 2018 7:16:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3321.28','34.48058009','-117.7314663','196.434','315','Aug 29 2018 7:18:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3397.44','34.50532079','-117.7909899','185.012','292.5','Aug 29 2018 7:20:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3381.51','34.53634858','-117.8582382','229.478','292.5','Aug 29 2018 7:22:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3365.61','34.56782699','-117.9271817','225.135','292.5','Aug 29 2018 7:24:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3356.09','34.56974745','-117.9900312','238.353','180','Aug 29 2018 7:26:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.53585505','-117.9157877','286.637','67.5','Aug 29 2018 7:28:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3340.25','34.60948706','-117.9229116','238.353','180','Aug 29 2018 7:32:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3349.75','34.55810666','-117.9282761','204.336','270','Aug 29 2018 7:34:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3340.25','34.59852219','-117.9932499','220.854','292.5','Aug 29 2018 7:36:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3352.92','34.63024735','-118.0624294','216.634','292.5','Aug 29 2018 7:38:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.67504025','-118.1266093','238.353','315','Aug 29 2018 7:40:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3359.26','34.71980095','-118.19139','229.478','315','Aug 29 2018 7:42:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3352.92','34.76578474','-118.2569647','229.478','315','Aug 29 2018 7:44:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3346.58','34.81043816','-118.3218312','233.884','315','Aug 29 2018 7:46:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3349.75','34.85564947','-118.3868909','242.885','315','Aug 29 2018 7:48:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3343.41','34.9006784','-118.4529805','229.478','315','Aug 29 2018 7:50:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3346.58','34.92015123','-118.5208082','204.336','225','Aug 29 2018 7:52:00:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3349.75','34.86854553','-118.4920335','286.637','112.5','Aug 29 2018 7:54:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3349.75','34.90371466','-118.4010959','291.838','22.5','Aug 29 2018 7:56:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3352.92','34.96744394','-118.4294629','216.634','292.5','Aug 29 2018 7:58:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3340.25','34.95463371','-118.5018826','216.634','202.5','Aug 29 2018 8:00:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3214.81','34.8894453','-118.4672713','291.838','135','Aug 29 2018 8:02:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','2976.66','34.82196093','-118.4023404','286.637','112.5','Aug 29 2018 8:04:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3015.77','34.79961276','-118.3046436','271.449','135','Aug 29 2018 8:06:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3009.74','34.74620461','-118.2295847','271.449','135','Aug 29 2018 8:08:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','2997.69','34.69474912','-118.1526589','276.443','112.5','Aug 29 2018 8:10:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3021.81','34.64860439','-118.071034','276.443','112.5','Aug 29 2018 8:12:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3030.87','34.61550593','-117.9798603','276.443','112.5','Aug 29 2018 8:14:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3015.77','34.57807302','-117.8819704','291.838','112.5','Aug 29 2018 8:16:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3015.77','34.53867674','-117.7875566','276.443','112.5','Aug 29 2018 8:18:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3030.87','34.50247765','-117.6955462','281.506','112.5','Aug 29 2018 8:20:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3033.9','34.46022749','-117.6062179','276.443','135','Aug 29 2018 8:22:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3024.83','34.40730214','-117.5300217','271.449','135','Aug 29 2018 8:24:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','3006.72','34.34492469','-117.471571','271.449','157.5','Aug 29 2018 8:26:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','2443.68','34.26383615','-117.4358654','286.637','180','Aug 29 2018 8:28:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','1871.9','34.1842711','-117.4454141','266.523','180','Aug 29 2018 8:30:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','1167.72','34.10933018','-117.4404788','252.144','180','Aug 29 2018 8:32:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','795.36','34.0389061','-117.4436331','242.885','157.5','Aug 29 2018 8:34:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','595.12','33.97681832','-117.443161','147.337','180','Aug 29 2018 8:36:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','717.31','33.97231221','-117.4028206','156.983','337.5','Aug 29 2018 8:38:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','578.1','33.94593','-117.4116397','174.096','337.5','Aug 29 2018 8:40:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','67.37','33.95222783','-117.4446201','5.012','292.5','Aug 29 2018 8:42:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','151.06','33.95215273','-117.4475598','9.07','270','Aug 29 2018 8:44:15:000PM',NULL)
INSERT INTO [GPSlog] ([messagecode],[messagecodelabel],[alt],[lat],[lon],[speed],[azimuth],[time],[Flight_Number])VALUES('0','Position Report','220.83','33.95125151','-117.447474','0','0','Aug 29 2018 8:46:15:000PM',NULL)
select * from gpslog
;
With
A as (SELECT
CASE WHEN Lead(messagecode, 1, 10) OVER (partition by [Flight_Number] order by time) = 10
THEN 20
ELSE messagecode END new_message_code,
* FROM GPSLog)
select * from A
SELECT
CASE WHEN Lead(messagecode, 1, 10) OVER (partition by [Flight_Number] order by time) = 10
THEN 20
ELSE messagecode END new_message_code,
* FROM GPSLog
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply