The query is thus; I have a set of logs in the database with a datetime indicating when they were processed. Our service, being not very reliable, seems to error a chunk of tasks over a certain amount of time. Now, a week later, I'd like to quickly determine when the service was erroring so we can try to find the log files for those tasks logged at those times and try to reprocess them.
For example if the datetimes were:
I need a query that would return:
So that I can use these datetimes to create a batch script to robocopy the logged files into a local directory to be reprocessed. How hard is this?
I've made the test data a little bit more realistic as it has an error overlapping a second boundary. This solution relies on the data being inserted into the heap in datetime order
Here goes a better attempt without a quirky update...
Again using @Phil Factor's test data:
Just throwing this one in because it was driving me crazy ;)
1:00:03 - 1 = 1:00:02
It is evident then that the first five entries are from the 1:00:02 "group" and the 6th belongs to the 1:00:19 "group" and so on down the list.
Borrowing heavily from Phil Factor's answer I have a partial solution but one that raises my own question.
I can get (sort of) the start and end times of the error bunches and I also get the single entry. I do this without the quirky update (which I thought was a cool and elegant solution).
I can "see" where the breaks are because of the sequential numbers but I just can't get the output the OP requested. How do I take this to the next level?
The following result set is produced:
While it is intuitively obvious where the start and end of the bunches are I can't take it logically forward for the final solution to the OP's question,
answered Jul 29 '10 at 01:48 PM
answered Jul 28 '10 at 07:02 PM