CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2
msr_overflow
msr_overflow

Reputation: 122

SQL query counting based on successive condition

I have a table that looks something like this:

+--------+-------+-------+
|TestName|TestRun|OutCome|
+--------+-------+-------+
| Test1  | 1     | Fail  |
+--------+-------+-------+
| Test1  | 2     | Fail  |
+--------+-------+-------+
| Test2  | 1     | Fail  |
+--------+-------+-------+
| Test2  | 2     | Pass  |
+--------+-------+-------+
| Test3  | 1     | Pass  |
+--------+-------+-------+
| Test3  | 2     | Fail  |
+--------+-------+-------+

The table is used for storing a brief summary of test results. I want to write a query (using T-SQL but any dialect is fine) that returns how many build each test has been failing. Use the example as input and it should return a result set like this:

+--------+----------+
|TestName|Regression|
+--------+----------+
| Test1  | 2        |
+--------+----------+
| Test2  | 0        |
+--------+----------+
| Test3  | 1        |
+--------+----------+

Note that the query should ONLY count current 'fail streak' instead of counting the total number of failures. Can assume MAX(TestRun) is the most recent run.

Any ideas?

Edit: grammar

Upvotes: 0

Views: 631

Answers (3)

Steve Homer
Steve Homer

Reputation: 3922

A bit ugly but it works.

create table dbo.tests
(
    TestName nvarchar(10) not null
    , TestRun int not null
    , OutCome nvarchar(10) not null
)

insert into dbo.tests
select 'Test1', 1, 'Fail'
union all 
select 'Test1', 3, 'Fail'
union all 
select 'Test2', 1, 'Fail'
union all 
select 'Test2', 3, 'Pass'
union all 
select 'Test3', 1, 'Pass'
union all 
select 'Test3', 3, 'Fail'

; with c (TestName, TestRun, OutCome, TestId)
as
(
    select TestName, TestRun, OutCome
            , dense_rank() over (order by TestRun) as TestId
    from dbo.tests
) 

select t.TestName, max(t.TestId) - mt.MaxPassedTestRun as FailedRuns
from c t
        inner join
            (
                select TestName, sum(TestId) as MaxPassedTestRun
                from
                    (
                        select TestName, TestId 
                        from c
                        where OutCome = 'Pass'

                        union all 

                        select TestName, 0 as TestRun
                        from c
                        where OutCome = 'Fail'
                    ) mt
                group by mt.TestName
            ) mt on t.TestName = mt.TestName
group by t.TestName, MaxPassedTestRun, mt.MaxPassedTestRun

Upvotes: 3

Fabio
Fabio

Reputation: 32455

Here is another approach using CTE:

;WITH streaks AS (
    SELECT t.Name, t.Run, t.Outcome 
    FROM TestRuns t
    INNER JOIN (SELECT Name, MAX(Run) AS MaxRun FROM TestRuns GROUP BY Name) maxes
    ON maxes.Name = t.Name AND maxes.MaxRun = t.Run

    UNION ALL

    SELECT t.Name, t.Run, t.Outcome
    FROM TestRuns t
    INNER JOIN streaks s ON s.Name = t.Name AND s.Outcome = 0
    WHERE t.Run = s.Run - 1 AND t.Outcome = 0
)

SELECT Name
, SUM(CASE WHEN Outcome = 0 THEN 1 ELSE 0 END) AS Regression
FROM streaks
GROUP BY Name

Here a SQL Fiddle with sample of data

Upvotes: 1

Sonam
Sonam

Reputation: 3466

select a.TestName,a.TestRun,a.Outcome From table1 a join 
Table1 b on a.TestName=b.testname          
and a.Outcome='Fail' 
and a.TestRun>b.TestRun

Upvotes: 1

Related Questions