Tricky SQL query - need to get time frames
- by Andrew
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!