Search for repeated occurrences with ranking functions

Question

Please help me generate the following query, which I have been struggling with for some time. Lets say that I have a simple table with a month number and information on whether there were any unsuccessful events in this particular month.

Below is 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 "repeated failure":

If a failure in the event occurs for at least 4 months within any 6 months, then the last month with such a failure is a “repeated failure”, my request should return the following result

 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 is the 1st second failure in month 6 (4 failures in the last 6 months).
• R2 -2nd second failure in month 7 (4 failures in the last 6 months).
• R3 -3rd repeated failure in month No. 11 (4 failures in the last 6 months).

R1 is the first 1st repeated failure in month No. 18, because repeated failures must be numbered again from the very beginning, when a new repeated failure occurs for the first time in the last 6 reporting periods.

Repeated failures are numbered sequentially, because based on its number, I should apply the appropriate factor:

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

Answer 1

I am sure that this can be improved, but it works. We essentially make two passes - the first to establish repeated failures, and the second to establish what repeated failure each of them is. Note that Intermediate2 can do away with this; I just set it apart for clarity. All code is one statement, my explanation alternates:

 ;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 determined for each month whether this is a repeated failure, counting the failures for six months and turning it on.

 ,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 preceding the present.

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

therefore, we can say that if this month is a repeated failure, then what is the power of the repeated failure.

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) 

The performance rating will depend on how much data you really have.

Answer 2

 ;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