In MS-SQL Server 2008, I have a table of events in the following format:
Controller | ExecutionTime | Result 1 | 2012-09-24 09:00:00 | 0 1 | 2012-09-24 09:01:00 | 0 2 | 2012-09-24 09:02:00 | 1
I need to analyze this table to produce a result that:
- Identifies each controller whose latest event was an error (result=1)
- Identifies how many consecutive failures that controller has had (# events since the most recent successful event)
I've made some progress toward this task, I can identify the controllers whose most recent event was a failure - see this SQL Fiddle. But I could use some help finding an approach to counting the # of consecutive failures that has led to this state.