Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

Thanks! John

share|improve this question
up vote 2 down vote accepted

Here's one solution. Not very efficient I'm afraid:

    MIN(t.ExecutionTime) AS FirstFaultInSeries,
    MAX(t.ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
        COALESCE(MAX(CASE WHEN Result = 0 THEN ExecutionTime END)
          AS LatestGood
    FROM t    
    GROUP BY Controller
  ) g
  JOIN t
    ON t.Controller = g.Controller
   AND t.ExecutionTime > g.LatestGood
    t.Controller ;

Tested: Fiddle

And another:

        Controller, ExecutionTime, Result,
        ROW_NUMBER() OVER (PARTITION BY Controller
                           ORDER BY ExecutionTime DESC
          AS Rn,
        ROW_NUMBER() OVER (PARTITION BY Controller, Result
                           ORDER BY ExecutionTime DESC
          AS RnResult
    FROM t
    MIN(ExecutionTime) AS FirstFaultInSeries,
    MAX(ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
    Result = 1
    Rn = RnResult
    Controller ;
share|improve this answer
Sheesh the speed with which you solve these is impressive. Thank you very much, these are very helpful and I'll learn a lot studying them. – John Jeheimer Jun 13 '13 at 23:06
BTW, if you are interested: The first solution presented above executes in about half the time of the second solution against my dataset. – John Jeheimer Jun 13 '13 at 23:21
Thnx for the info. The efficiency of the various methods will depend mainly on data distribution and indexes. – ypercubeᵀᴹ Jun 13 '13 at 23:28

This is the typical "islands and gaps" problem. Itzik Ben Gan describes this problem and its solution in a chapter of "MVP deep dives".

It's a great book and Itzik's chapter alone would be a great reason to buy it. A sample of that chapter can be found here:

A description of the algorithm can be found here with three different implementations.

Basically, it's all about using ranking functions to assign a number to each partition of consecutive rows with the same value in one or more columns.

I'm sorry I can't post a solution based on your fiddle, but this should put you in the right direction and it's the best I can do from my mobile.

share|improve this answer
I appreciate that you gave me insight to the broader context of this problem and the overall patterns that apply! I wasn't aware of the name - I'll start researching now. – John Jeheimer Jun 13 '13 at 22:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.