XdC9t7Hy

1CREATE TABLE `testresult` (
2  `TestResultPK` int(11) NOT NULL AUTO_INCREMENT,
3  `Passed` tinyint(1) NOT NULL,
4  `CreatedDate` datetime DEFAULT NULL,
5  PRIMARY KEY (`TestResultPK`),
6  UNIQUE KEY `TestResultPK` (`TestResultPK`),
7  KEY `CreatedDateIndex` (`CreatedDate`)
8) ENGINE=InnoDB AUTO_INCREMENT=94755 DEFAULT CHARSET=latin1;
9CREATE TABLE `testcasedataresult` (
10  `TestCaseDataResultPK` int(11) NOT NULL AUTO_INCREMENT,
11  `Passed` tinyint(1) DEFAULT NULL,
12  `RetryRun` int(11) DEFAULT NULL,
13  `TestCaseDataFK` int(11) DEFAULT NULL,
14  `TestResultFK` int(11) DEFAULT NULL,
15  PRIMARY KEY (`TestCaseDataResultPK`),
16  UNIQUE KEY `TestCaseDataResultPK` (`TestCaseDataResultPK`),
17  KEY `TestCaseDataFK` (`TestCaseDataFK`),
18  KEY `TestResultFK` (`TestResultFK`),
19  #CONSTRAINT `FK469417F32EBE75FE` FOREIGN KEY (`TestCaseDataFK`) REFERENCES `testcasedata` (`TestCaseDataPK`),
20  CONSTRAINT `FK469417F3C87DE8C6` FOREIGN KEY (`TestResultFK`) REFERENCES `testresult` (`TestResultPK`)
21) ENGINE=InnoDB AUTO_INCREMENT=20193325 DEFAULT CHARSET=latin1; 
22     
23DELIMITER $$
24CREATE DEFINER=`system`@`%` PROCEDURE `failedtestcasestatistics`(in trWhere LONGTEXT)
25BEGIN
26
27  DROP TABLE if EXISTS failedtcdr_foreach_tr;
28  DROP TABLE if EXISTS alltcdr;
29  SET @failedtcdrSql = CONCAT('
30  create temporary table failedtcdr_foreach_tr
31  select tcdr.TestResultFK, max(tcdr.TestCaseDataResultPK)  MaxTestCaseDataResultPK
32  from testcasedataresult tcdr, testresult tr, (', trWhere, ') trWhereTable
33  where 
34    tcdr.TestResultFK = tr.TestResultPK and
35    tr.Passed = 0 and
36    trWhereTable.TestResultPK = tcdr.TestResultFK
37  group by tcdr.TestResultFK');
38  PREPARE failedTcdrStmt FROM @failedtcdrSql;
39  EXECUTE failedTcdrStmt;
40  DEALLOCATE PREPARE failedTcdrStmt;
41
42  SET @alltcdrSql = CONCAT('
43  create temporary table alltcdr
44  select tcdr.TestCaseDataFK as TestCaseDataFK, count(*) as total from testcasedataresult tcdr, (', trWhere, ') trWhereTable
45  where tcdr.TestResultFK = trWhereTable.TestResultPK
46  group by tcdr.TestCaseDataFK
47  ');
48
49  PREPARE allTcdrStmt FROM @alltcdrSql;
50  EXECUTE allTcdrStmt;
51  DEALLOCATE PREPARE allTcdrStmt;
52
53#select * from alltcdr;
54
55select 
56    failedGroup.TestCaseDataFK,
57    failedGroup.failures,
58    allt.total as total
59from
60    (select 
61        tcdr.TestCaseDataFK TestCaseDataFK, count(*) failures
62    from
63        failedtcdr_foreach_tr trs, testcasedataresult tcdr
64    where
65        trs.MaxTestCaseDataResultPK = tcdr.TestCaseDataResultPK
66    group by tcdr.TestCaseDataFK) failedGroup,
67    alltcdr as allt
68where
69    failedGroup.TestCaseDataFK = allt.TestCaseDataFK;
70
71  DROP TABLE failedtcdr_foreach_tr;
72  DROP TABLE alltcdr;
73END$$
74DELIMITER ; 
75     
76CALL `failedtestcasestatistics`('select tr.TestResultPK from testresult tr');