IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.
Course Of The Month
2 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Getting SQL Rows for Flight Data
Posted on 2016-08-27
I have a GPS device that captures data when we fly. The data is published to a web service, which I store in a SQL database. I know how to break the data into flight based on looking at it, but I am trying to tell SQL how to do it.
When the GPS unit fires up is sends a position report along with a field called MessageCode which is set to a value of 10 for the first record. The value then changes other values throughout the flight. The next flight is determined by the next MessageCode 10.
What I need:
1.) I need a select statement to grab all of the rows from the first messagecode 10, through the record just PRIOR to the next messageCode 10. This would be consider a single flight.
I am happy to send example data but I do not want to post it publically.
When the GPS unit fires up is sends a position report along with a field called MessageCode which is set to a value of 10 for the first record. The value then changes other values throughout the flight. The next flight is determined by the next MessageCode 10.
What I need:
1.) I need a select statement to grab all of the rows from the first messagecode 10, through the record just PRIOR to the next messageCode 10. This would be consider a single flight.
I am happy to send example data but I do not want to post it publically.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
5-
5
-
2
- +1
15 Comments
If you added an identity field to the table, call it recordid, then
select RecordID from <tablename> where Recordtype = 10 would give you a list of flights.
If you know the first 10 record, then you could query as follows
select * from <tablename> where recordtype <> 10 and
recordid >firstflightrecordid
and recordid < (select min(recordid) from <tablename> where recordid > firstflightrecordid and recordtype = 10)
select RecordID from <tablename> where Recordtype = 10 would give you a list of flights.
If you know the first 10 record, then you could query as follows
select * from <tablename> where recordtype <> 10 and
recordid >firstflightrecordid
and recordid < (select min(recordid) from <tablename> where recordid > firstflightrecordid and recordtype = 10)
Active today
>I am happy to send example data but I do not want to post it publically.
You can always just dummy it up, like what I did in this article with a client's data and is now unrecognizable to who the carrier is.
Some questions really need either a screen shot image or sample data.
You can always just dummy it up, like what I did in this article with a client's data and is now unrecognizable to who the carrier is.
Some questions really need either a screen shot image or sample data.
Active today
CREATE TABLE YourTable
(ID int, MessageCode int, Lat int, long int)
;
INSERT INTO YourTable
(ID, MessageCode, Lat, long)
VALUES
(1, 10, 111, 111),
(2, 20, 112, 112),
(3, 20, 113, 113),
(4, 20, 114, 114),
(5, 10, 115, 115),
(6, 20, 116, 116),
(7, 20, 117, 117),
(8, 10, 118, 118),
(9, 20, 119, 119),
(10, 20, 120, 120),
(11, 20, 121, 121),
(12, 20, 122, 122),
(13, 20, 123, 123)
;
select
*
, case when MessageCode = 10 then 'Start' else 'End' end as type
from (
select
*, lead(MessageCode,1) over(order by ID) nxtmc
from YourTable
) d
where (MessageCode = 10
OR
nxtmc = 10
)
| id | messagecode | lat | long | nxtmc | type |
|----|-------------|-----|------|-------|-------|
| 1 | 10 | 111 | 111 | 20 | Start |
| 4 | 20 | 114 | 114 | 10 | End |
| 5 | 10 | 115 | 115 | 20 | Start |
| 7 | 20 | 117 | 117 | 10 | End |
| 8 | 10 | 118 | 118 | 20 | Start |
It isn't hard to provide "sample data" note it is SAMPLE (meaning both: small and representatve) and because it is sample we need it does not need to reveal anything private.
HOWEVER: IT HAS TO REPRESENT YOUR TABLE.
Note how I have assume ID increases for each row. Is that true of your table?
additionally, why not give us the table name to work with too?
Another thing you might note is that without an "expected result" we could propose any old query, but it may not suit your purpose. So, also providing your "expected result" will help you get the best outcome for your question (and we don't have to guess what you want either).
Moral of this story: providing "sample data" and "expected result" = fastest good outcome
Active today
Attached is sample data.
What I need specifically is a query that will look at each DEVICE ID and determine the START and ENDING for each flight for that DeviceID
Start = MessageCode = 10
End = The row chronologically previous to the NEXT MessageCode 10
Hope that makes sense.
Data.csv
What I need specifically is a query that will look at each DEVICE ID and determine the START and ENDING for each flight for that DeviceID
Start = MessageCode = 10
End = The row chronologically previous to the NEXT MessageCode 10
Hope that makes sense.
Data.csv
Active today
The headers for the CSV above are
id,timestamp,latitude,long
id,timestamp,latitude,long
Active today
What is the "expected result" for 1, 2, 3 and 4 below?
please note I added "T" in front of timestamp and device to avoid Excel converting into scientific notation
please note I added "T" in front of timestamp and device to avoid Excel converting into scientific notation
Active today
I don't have free time at the moment but here is a sample of your data for others to use, it includes the T I refer to above. I suggest you provide the "expected result".
DECLARE @YourTableHere table
([id] int, [timestamp] varchar(14), [latitude] numeric, [longitude] numeric, [altitude] numeric, [course] int, [speed] numeric, [messagecode] int, [DeviceID] varchar(16))
;
INSERT INTO @YourTableHere
([id], [timestamp], [latitude], [longitude], [altitude], [course], [speed], [messagecode], [DeviceID])
VALUES
(1620, 'T1471817400000', 37.36195922, -121.9346809, 91.86352, 0, 0, 10, 'T600868120187500'),
(1621, 'T1471817580000', 37.36147642, -121.9336295, 34.3175864, 67.5, 6.008641496, 0, 'T600868120187500'),
(1623, 'T1471817610000', 37.36126184, -121.9324923, 27.6574812, 112.5, 9.435208618, 0, 'T600868120187500'),
(1626, 'T1471817640000', 37.36206651, -121.931076, 27.6574812, 45, 16.92873186, 0, 'T600868120187500'),
(1643, 'T1471818180000', 37.19832301, -121.8649435, 7142.38868, 135, 91.24247382, 10, 'T600868120187500'),
(1655, 'T1471818225000', 37.18890309, -121.8531632, 7470.603914, 135, 95.93955976, 0, 'T600868120187500'),
(1647, 'T1471818255000', 37.17901111, -121.8412971, 7728.773213, 135, 101.9233632, 0, 'T600868120187500'),
(1644, 'T1471818285000', 37.16753125, -121.8263841, 7684.022556, 135, 123.9082524, 0, 'T600868120187500'),
(1645, 'T1471818315000', 37.15505362, -121.8105912, 7764.632794, 112.5, 121.5632192, 0, 'T600868120187500'),
(1648, 'T1471818345000', 37.14874506, -121.7907643, 7818.536996, 112.5, 126.287303, 0, 'T600868120187500'),
(1649, 'T1471818375000', 37.14227557, -121.7693496, 7836.515999, 112.5, 126.287303, 0, 'T600868120187500'),
(1650, 'T1471818405000', 37.13816643, -121.7487073, 7953.871646, 112.5, 123.9082524, 0, 'T600868120187500'),
(1652, 'T1471818435000', 37.12772727, -121.7312837, 7926.739099, 135, 131.1474559, 0, 'T600868120187500'),
(1654, 'T1471818465000', 37.11350083, -121.7151904, 7854.52781, 135, 136.1469178, 0, 'T600868120187500'),
(1754, 'T1471824420000', 34.52862382, -118.7055159, 7004.5934, 157.5, 134.6911937, 10, 'T600868120187500'),
(1761, 'T1471824540000', 34.47623491, -118.6797881, 7194.88212, 157.5, 130.3466997, 10, 'T600868120187500'),
(1788, 'T1471824900000', 34.28012252, -118.5334468, 2602.32948, 135, 123.9082524, 0, 'T600868120187500'),
(1772, 'T1471824930000', 34.26960826, -118.5186839, 2289.698236, 135, 110.3326536, 0, 'T600868120187500'),
(1770, 'T1471824960000', 34.25828934, -118.5066032, 2134.678546, 135, 99.89852448, 0, 'T600868120187500'),
(1769, 'T1471824990000', 34.2470026, -118.496604, 1736.679846, 157.5, 92.09884562, 0, 'T600868120187500'),
(1771, 'T1471825035000', 34.23464298, -118.4929132, 1329.790069, 180, 88.37422223, 0, 'T600868120187500'),
(1773, 'T1471825065000', 34.22185421, -118.4915185, 961.4173536, 180, 88.37422223, 0, 'T600868120187500'),
(1775, 'T1471825095000', 34.2109108, -118.4904456, 623.3924084, 180, 58.31211626, 0, 'T600868120187500'),
(1776, 'T1471825125000', 34.20647979, -118.490746, 468.7336108, 270, 8.852595015, 0, 'T600868120187500'),
(1777, 'T1471825155000', 34.20639396, -118.4909606, 482.1850548, 270, 3.797517581, 0, 'T600868120187500'),
(1781, 'T1471825185000', 34.20786381, -118.4913039, 562.7952936, 0, 11.8212786, 0, 'T600868120187500'),
(1778, 'T1471825215000', 34.20940876, -118.4914756, 738.024958, 0, 10.02268183, 0, 'T600868120187500'),
(1779, 'T1471825245000', 34.21143651, -118.4916687, 771.7848016, 0, 15.60367739, 0, 'T600868120187500'),
(1780, 'T1471825275000', 34.21323895, -118.4918618, 785.3018624, 0, 13.67873068, 0, 'T600868120187500'),
(1782, 'T1471825305000', 34.21545982, -118.4921622, 792.0603928, 0, 14.3126402, 0, 'T600868120187500'),
(1783, 'T1471825335000', 34.21682239, -118.4928274, 798.8189232, 270, 10.61609458, 0, 'T600868120187500'),
(1784, 'T1471825365000', 34.21706915, -118.494823, 805.5774536, 292.5, 8.852595015, 0, 'T600868120187500'),
(1789, 'T1471825425000', 34.21816349, -118.495574, 812.3687924, 292.5, 3.251081097, 0, 'T600868120187500'),
(3339, 'T1471920060000', 34.34904456, -119.0586233, 219.81628, 0, 0, 10, 'T600868120187500'),
(3406, 'T1471924920000', 34.21809912, -118.4957242, 774.27824, 0, 0, 10, 'T600868120187500'),
(4973, 'T1472422740000', 34.21007395, -119.0784287, 65.6168, 0, 0, 10, 'T600868120187500'),
(4974, 'T1472422800000', 34.21009541, -119.0784287, 74.2782176, 0, 0, 0, 'T600868120187500'),
(4976, 'T1472422830000', 34.21009541, -119.0784502, 67.6181124, 0, 0, 0, 'T600868120187500'),
(4977, 'T1472422860000', 34.21017051, -119.0783, 67.6181124, 22.5, 3.251081097, 0, 'T600868120187500'),
(4978, 'T1472422890000', 34.21079278, -119.0781498, 74.2782176, 22.5, 5.451405872, 0, 'T600868120187500'),
(4983, 'T1472422920000', 34.21131849, -119.0783644, 74.2782176, 270, 10.61609458, 0, 'T600868120187500'),
(4981, 'T1472422950000', 34.21129704, -119.081347, 74.2782176, 270, 18.28996346, 0, 'T600868120187500'),
(4980, 'T1472422980000', 34.21130776, -119.0835357, 80.9383228, 270, 4.346113893, 0, 'T600868120187500'),
(4982, 'T1472423010000', 34.21172619, -119.0841794, 80.9383228, 315, 2.163067742, 0, 'T600868120187500'),
(4984, 'T1472423040000', 34.21172619, -119.0841794, 80.9383228, 0, 0, 0, 'T600868120187500'),
(4987, 'T1472423070000', 34.21172619, -119.0841794, 80.9383228, 0, 0, 0, 'T600868120187500'),
(4988, 'T1472423100000', 34.21172619, -119.0841794, 80.9383228, 0, 0, 0, 'T600868120187500'),
(4989, 'T1472423130000', 34.21172619, -119.0841794, 74.2782176, 0, 0, 0, 'T600868120187500'),
(4991, 'T1472423220000', 34.21270251, -119.0846086, 67.6181124, 0, 0.539957, 0, 'T600868120187500')
;
select
*
from @YourTableHere fd1
Active today
Those must be in flight resets of the device. I am not sure how to handle. I guess ultimately they should be ignore but I don't think that will work. The messagecode 10 is the starting event. So perhaps we just need to treat it that way no matter what.
Active today
I am not sure how to handle.
But you must make the decision (that is your role, as it is your question)
.... and again: I ask that you supply the "expected result"
Not in words; but the table that you would expect the wanted query to supply.
But you must make the decision (that is your role, as it is your question)
.... and again: I ask that you supply the "expected result"
Not in words; but the table that you would expect the wanted query to supply.
Active today
Using the sample data at ID: 417741 above
select
fd1.id startid, oa.nxt_startid
from @YourTableHere fd1
outer apply (
select top(1) id nxt_startid
from @YourTableHere fd2
where fd2.DeviceID = fd1.DeviceID
and fd2.id > fd1.id
and fd2.messagecode = 10
order by timestamp
) oa
where fd1.messagecode = 10
startid nxt_startid
------- -----------
1620 1643
1643 1754
1754 1761
1761 3339
3339 3406
3406 4973
4973 null
PortletPaul was just expanding on my basic solution. I should get at least 100 points of the question.
Active today
I would agree with twol. His comment did contain the same basic premise. I agree that the points should be split 400 to PortletPaul and 100 to twol. Although I will make the comment the PortletPaul could have been a little more friendly in his comments. Public flaming has no place when I am PAYING in points for what is provided. If you do not have time to answer the question or be nice about it, then don't respond and save yourself the grief.
Active today
As stated before, publically making a person look stupid is not necessary. Especially when you are getting something for your time. My suggestion to PortletPaul is that in the future if you don't have the time to work an issue then DON'T. I certainly appreciate what you have provided but doing it a little more respectfully would have been nice. If I had all the answers I would not have come to EE.
Featured Post
Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| databases from multiple applications - same instance | 6 | 40 | |
| T-SQL: need to reset a declared variable | 4 | 33 | |
| SQL Server set parent recort in select with row number | 8 | 29 | |
| Error converting data type varchar to numeric. | 4 | 14 |
This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Suggested Courses
Course of the Month2 days, 5 hours left to enroll
710 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.