i am trying to generate a report that shows idle time for some vehicles... (i can do all the calcs etc but......
I am not sure if this is possible but i need to group this data by street, however vehicles often visit the same street more than once in a day and therefore cannot group those two visits together. I need to somehow group them together while the street is the same, when it changes... its a new group and so on.
DATA
This is just a sample of the data, it is produced by a query that finds records where the speed is zero.
If these are orderd by the date/time as shown, aren't they already grouped the way you want. When you process the results, just detect a change in the street name. Also, what is your ultimate goal/result you want to get from the data to help pin down what a query needs to accomplish?
__________________
If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.
The row with the NULL in it is the row where the ROLLUP value is inserted in the result set, if that is what the question is?
__________________
If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.
Location: Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
Posts: 7,686
Thanks: 42
Thanked 637 Times in 625 Posts
I really don't think you want to include "Time" in your GROUP BY clause-- that would put every different time into a new group. I realize this is a contradiction to my first reply on this thread, but I think now I actually understand what it is you're trying to do
What you really need is a new field that indicates a grouping of measurements that is unique to its group. Right now you want to differentiate 10 or so rows from another 10 or so rows, and the only thing to go off of is the datetime fields. What if a vehicle visits a street twice and ends up not visiting any other street in between? A person can look at the pattern and identify two separate visits, but it's tougher for a machine. And I realize adding a new field isn't feasible....
If you were to group all rows with a timestamp inside the same hour, and the vehicle and street are the same of course, that would be a start, though you'll get different groups for the same visit when it goes into 2 different hours...
Sometimes it's just easier bringing the data into your scripting language and finishing up after you've run the query. If it were me I'd simply use PHP to dump all the data into an array sorted by time, loop through the array, and check to see when the street changes.
Before you post, read our: 
