Solved

Getting SQL Rows for Flight Data

Posted on 2016-08-27
15
50 Views
Last Modified: 2016-09-18
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.
0
Comment
Question by:sgroner
  • 5
  • 5
  • 2
  • +1
15 Comments
 
LVL 7

Assisted Solution

by:twol
twol earned 100 total points (awarded by participants)
Comment Utility
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)
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
>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.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points (awarded by participants)
Comment Utility
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)
;

Open in new window

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
      )

Open in new window

| 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 |

Open in new window

see http://sqlfiddle.com/#!15/49d12/3

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
0
 
LVL 1

Author Comment

by:sgroner
Comment Utility
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
0
 
LVL 1

Author Comment

by:sgroner
Comment Utility
The headers for the CSV above are

id,timestamp,latitude,longitude,altitude,course,speed,messagecode,DeviceID
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 400 total points (awarded by participants)
Comment Utility
What is the "expected result" for 1, 2, 3 and 4 below?sample-data-explain.pngplease note I added "T" in front of timestamp and device to avoid Excel converting into scientific notation
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Open in new window

0
 
LVL 1

Author Comment

by:sgroner
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

Open in new window

startid nxt_startid 
------- ----------- 
1620    1643        
1643    1754        
1754    1761        
1761    3339        
3339    3406        
3406    4973        
4973    null        

Open in new window

0
 
LVL 7

Expert Comment

by:twol
Comment Utility
PortletPaul was just expanding on my basic solution. I should get at least 100 points of the question.
0
 
LVL 1

Author Comment

by:sgroner
Comment Utility
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.
0
 
LVL 1

Author Comment

by:sgroner
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now