sql server - SQL query counting based on successive condition -
i have table looks this:
+--------+-------+-------+ |testname|testrun|outcome| +--------+-------+-------+ | test1 | 1 | fail | +--------+-------+-------+ | test1 | 2 | fail | +--------+-------+-------+ | test2 | 1 | fail | +--------+-------+-------+ | test2 | 2 | pass | +--------+-------+-------+ | test3 | 1 | pass | +--------+-------+-------+ | test3 | 2 | fail | +--------+-------+-------+
the table used storing brief summary of test results. want write query (using t-sql dialect fine) returns how many build each test has been failing. use example input , should return result set this:
+--------+----------+ |testname|regression| +--------+----------+ | test1 | 2 | +--------+----------+ | test2 | 0 | +--------+----------+ | test3 | 1 | +--------+----------+
note query should count current 'fail streak' instead of counting total number of failures. can assume max(testrun) recent run.
any ideas?
edit: grammar
a bit ugly works.
create table dbo.tests ( testname nvarchar(10) not null , testrun int not null , outcome nvarchar(10) not null ) insert dbo.tests select 'test1', 1, 'fail' union select 'test1', 3, 'fail' union select 'test2', 1, 'fail' union select 'test2', 3, 'pass' union select 'test3', 1, 'pass' union select 'test3', 3, 'fail' ; c (testname, testrun, outcome, testid) ( select testname, testrun, outcome , dense_rank() on (order testrun) testid dbo.tests ) select t.testname, max(t.testid) - mt.maxpassedtestrun failedruns c t inner join ( select testname, sum(testid) maxpassedtestrun ( select testname, testid c outcome = 'pass' union select testname, 0 testrun c outcome = 'fail' ) mt group mt.testname ) mt on t.testname = mt.testname group t.testname, maxpassedtestrun, mt.maxpassedtestrun
Comments
Post a Comment