Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

2 Answers 2

up vote 2 down vote accepted

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

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

Tested: Fiddle

And another:

WITH cte AS
  ( SELECT 
        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
  ) 
SELECT 
    Controller,
    MIN(ExecutionTime) AS FirstFaultInSeries,
    MAX(ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
FROM
    cte 
WHERE
    Result = 1
  AND
    Rn = RnResult
GROUP BY
    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
add comment

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: http://www.manning.com/nielsen/SampleChapter5.pdf

A description of the algorithm can be found here http://blogs.msdn.com/b/samlester/archive/2012/09/04/tsql-solve-it-your-way-gaps-and-islands-with-a-twist.aspx 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
add comment

Your Answer

 
discard

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.