Finding repeated occurrences with ranking functions

Question

Please help me generate the following query i've been struggling with for some time now. Lets' say I have a simple table with month number and information whether there were any failed events in this particular month

Below a script to generate sample data:

WITH DATA(Month, Success) AS
(
    SELECT  1, 0 UNION ALL
    SELECT  2, 0 UNION ALL
    SELECT  3, 0 UNION ALL
    SELECT  4, 1 UNION ALL
    SELECT  5, 1 UNION ALL
    SELECT  6, 0 UNION ALL
    SELECT  7, 0 UNION ALL
    SELECT  8, 1 UNION ALL
    SELECT  9, 0 UNION ALL
    SELECT 10, 1 UNION ALL
    SELECT 11, 0 UNION ALL
    SELECT 12, 1 UNION ALL
    SELECT 13, 0 UNION ALL
    SELECT 14, 1 UNION ALL
    SELECT 15, 0 UNION ALL
    SELECT 16, 1 UNION ALL
    SELECT 17, 0 UNION ALL
    SELECT 18, 0
)

Given the definition of a "repeated failure ":

When event failure occurs during at least 4 months in any 6 months period then the last month with such failure is a "repeated failure" my query should return the following output

Month   Success RepeatedFailure
1       0   
2       0   
3       0   
4       1   
5       1   
6       0       R1
7       0       R2
8       1   
9       0   
10      1   
11      0       R3
12      1   
13      0   
14      1   
15      0   
16      1   
17      0
18      0       R1

where:

R1 -1st repeated failure in month no 6 (4 failures in last 6 months).
R2 -2nd repeated failure in month no 7 (4 failures in last 6 months).
R3 -3rd repeated failure in month no 11 (4 failures in last 6 months).

R1 -again 1st repeated failure in month no 18 because Repeated Failures should be again numbered from the beginning when new Repeated Failure occurs for the first time in last 6 reporting periods

Repeated Failures are numerated consecutively because based on its number i must apply appropriate multiplier:

1st repated failure - X2
2nd repeated failure - X4
3rd and more repeated failure -X5.

What version of SQL Server? 2012 has additional ranking functions compared to 2005-2008. — Martin Smith, Jun 25 '12 at 10:15
I've edited your data to (I think) fix it - please edit further if I've made a mistake — AakashM, Jun 25 '12 at 11:13
Considering months 6-12. If 11 and 12's success values were swapped, would 12 be labelled R2 or R3? (I could see it being either - R2 because it only has one other repeated failure in the past 6 months, or R3 because that previous failure was labelled R2). — Damien_The_Unbeliever, Jun 25 '12 at 12:31
Damien after swapping the values the 12 would not be considered as repeated fail because it wouldnt match the definition ("When event failure occurs during at least 4 months in any 6 months period"). The 6 months period from 7 to 12 (inclusive) would then have the following sequence 0, 1, 0,1, 1, 0 containing only 3 failed periods — Tomasz Zadrożny, Jun 25 '12 at 13:08

score 2 · Accepted Answer · answered Jun 25 '12 at 11:27

I'm sure this can be improved, but it works. We essentially do two passes - the first to establish repeated failures, the second to establish what kind of repeated failure each is. Note that Intermediate2 can definitely be done away with, I've only separated it out for clarity. All the code is one statement, my explanation is interleaved:

;WITH DATA(Month, Success) AS
-- assuming your data  as defined (with my edit)
,Intermediate AS 
(
SELECT
    Month,
    Success,
    -- next column for illustration only
    (SELECT SUM(Success) 
     FROM DATA hist 
     WHERE curr.Month - hist.Month BETWEEN 0 AND 5) 
        AS SuccessesInLastSixMonths,
    -- next column for illustration only
    6 - (SELECT SUM(Success) 
     FROM DATA hist 
     WHERE curr.Month - hist.Month BETWEEN 0 AND 5) 
        AS FailuresInLastSixMonths,
    CASE WHEN 
            (6 - (SELECT SUM(Success) 
                    FROM DATA hist 
                    WHERE curr.Month - hist.Month BETWEEN 0 AND 5)) 
            >= 4 
            THEN 1
            ELSE 0 
    END AS IsRepeatedFailure
FROM DATA curr 
-- No real data until month 6
WHERE curr.Month > 5
)

At this point we have established, for each month, whether it's a repeated failure, by counting the failures in the six months up to and including it.

,Intermediate2 AS
(
SELECT 
    Month,
    Success,
    IsRepeatedFailure,
    (SELECT SUM(IsRepeatedFailure) 
        FROM Intermediate hist 
        WHERE curr.Month - hist.Month BETWEEN 0 AND 5) 
        AS RepeatedFailuresInLastSixMonths
FROM Intermediate curr
)

Now we have counted the number of repeated failures in the six months leading up to now

SELECT
    Month,
    Success,
    CASE IsRepeatedFailure 
        WHEN 1 THEN 'R' + CONVERT(varchar, RepeatedFailuresInLastSixMonths) 
        ELSE '' END
    AS RepeatedFailureText
FROM Intermediate2

so we can say, if this month is a repeated failure, what cardinality of repeated failure it is.

Result:

Month       Success     RepeatedFailureText
----------- ----------- -------------------------------
6           0           R1
7           0           R2
8           1           
9           0           
10          1           
11          0           R3
12          1           
13          0           
14          1           
15          0           
16          1           
17          0           
18          0           R1

(13 row(s) affected)

Performance considerations will depend on on how much data you actually have.

Thank You AakashM. Work's like a charm – Tomasz Zadrożny Jun 25 '12 at 13:54 — Tomasz Zadrożny, Jun 25 '12 at 13:54

Asif · Answer 2 · 2012-06-25T12:51:09.807

;WITH DATA(Month, Success) AS
(
    SELECT  1, 0 UNION ALL
    SELECT  2, 0 UNION ALL
    SELECT  3, 0 UNION ALL
    SELECT  4, 1 UNION ALL
    SELECT  5, 1 UNION ALL
    SELECT  6, 0 UNION ALL
    SELECT  7, 0 UNION ALL
    SELECT  8, 1 UNION ALL
    SELECT  9, 0 UNION ALL
    SELECT 10, 1 UNION ALL
    SELECT 11, 0 UNION ALL
    SELECT 12, 1 UNION ALL
    SELECT 13, 0 UNION ALL
    SELECT 14, 1 UNION ALL
    SELECT 15, 0 UNION ALL
    SELECT 16, 1 UNION ALL
    SELECT 17, 0 UNION ALL
    SELECT 18, 0
)

SELECT DATA.Month,DATA.Success,Isnull(convert(Varchar(10),b.result),'') +         
Isnull(CONVERT(varchar(10),b.num),'') RepeatedFailure
FROM (
SELECT *, ROW_NUMBER() over (order by Month) num FROM 
( Select * ,(case  when (select sum(Success) 
from DATA where MONTH>(o.MONTH-6) and MONTH<=(o.MONTH)  ) <= 2 
and o.MONTH>=6 then 'R' else  '' end) result
from DATA o
) a where result='R'
) b 
right join DATA on DATA.Month = b.Month
order by DATA.Month

Asif that's a neat solution but doesn't return the desired output. In 18th month there is a repeated failure but not R4. It should be R1. It is the first occurrence of a repeated failure in the last 6 months period (13-18) — Tomasz Zadrożny, Jun 25 '12 at 13:19

Finding repeated occurrences with ranking functions

2 Answers2