Tricky SQL query - need to get time frames

Posted by Andrew on Stack Overflow See other posts from Stack Overflow or by Andrew
Published on 2012-10-31T22:28:13Z Indexed on 2012/10/31 23:00 UTC
Read the original article Hit count: 205

Filed under:
|

I am stumbled upon a problem, when I need a query which will produce a list of speeding time frames.

Here is the data example

[idgps_unit_location]   [dt]    [idgps_unit]    [lat]   [long]  [speed_kmh]
26  10/18/2012 18:53    2   47  56  30
27  10/18/2012 18:53    2   49  58  31
28  10/18/2012 18:53    2   28  37  15
29  10/18/2012 18:54    2   56  65  33
30  10/18/2012 18:54    2   152 161 73
31  10/18/2012 18:55    2   134 143 64
32  10/18/2012 18:56    2   22  31  12
36  10/18/2012 18:59    2   98  107 47
37  10/18/2012 18:59    2   122 131 58
38  10/18/2012 18:59    2   91  100 44
39  10/18/2012 19:00    2   190 199 98
40  10/18/2012 19:01    2   194 203 101
41  10/18/2012 19:02    2   182 191 91
42  10/18/2012 19:03    2   162 171 78
43  10/18/2012 19:03    2   174 183 83
44  10/18/2012 19:04    2   170 179 81
45  10/18/2012 19:05    2   189 198 97
46  10/18/2012 19:06    2   20  29  10
47  10/18/2012 19:07    2   158 167 76
48  10/18/2012 19:08    2   135 144 64
49  10/18/2012 19:08    2   166 175 79
50  10/18/2012 19:09    2   9   18  5
51  10/18/2012 19:09    2   101 110 48
52  10/18/2012 19:09    2   10  19  7
53  10/18/2012 19:10    2   32  41  20
54  10/18/2012 19:10    1   54  63  85
55  10/19/2012 19:11    2   55  64  50

I need a query that would convert this table into the following report that shows frames of time when speed was >80:

[idgps_unit]    [dt_start]  [lat_start] [long_start]    [speed_start]   [dt_end]    [lat_end]   [long_end]  [speed_end] [speed_average]
2   10/18/2012 19:00    190 199 98  10/18/2012 19:02    182 191 91  96.66666667
2   10/18/2012 19:03    174 183 83  10/18/2012 19:05    189 198 97  87
1   10/18/2012 19:10    54  63  85  10/18/2012 19:10    54  63  85  85

Now, what have I tried? I tried putting this into separate tables, queries and do some joins... Nothing works and I am very frustrated... I am not even sure if this could be done via the query. Asking for the expert help!

© Stack Overflow or respective owner

Related posts about mysql

Related posts about tsql