| Line 9... |
Line 9... |
| 9 |
dd.project,
|
9 |
dd.project,
|
| 10 |
SUM(dd.passed) AS passed_count,
|
10 |
SUM(dd.passed) AS passed_count,
|
| 11 |
SUM(dd.run) AS run_count,
|
11 |
SUM(dd.run) AS run_count,
|
| 12 |
SUM(dd.est_run_time) as est_run_total,
|
12 |
SUM(dd.est_run_time) as est_run_total,
|
| 13 |
SUM(dd.act_run_time) as act_run_total,
|
13 |
SUM(dd.act_run_time) as act_run_total,
|
| - |
|
14 |
SUM(dd.act_inv_time) as act_inv_total,
|
| 14 |
CASE WHEN SUM(dd.run) = 0 THEN 0 ELSE SUM(dd.passed)* 100.0/SUM(dd.run) END AS test_rate_pass,
|
15 |
CASE WHEN SUM(dd.run) = 0 THEN 0 ELSE SUM(dd.passed)* 100.0/SUM(dd.run) END AS test_rate_pass,
|
| 15 |
CASE WHEN SUM(dd.act_run_time) = 0 THEN 0 ELSE SUM(dd.est_run_time)* 1.0/SUM(dd.act_run_time) END AS test_run_efficiency,
|
16 |
CASE WHEN SUM(dd.act_run_time) = 0 THEN 0 ELSE SUM(dd.est_run_time)* 1.0/SUM(dd.act_run_time) END AS test_run_efficiency,
|
| 16 |
CASE WHEN SUM(dd.act_run_time) = 0 THEN 0 ELSE SUM(dd.act_inv_time)* 1.0/SUM(dd.act_run_time) END AS issue_inv_loading
|
17 |
CASE WHEN SUM(dd.act_run_time) = 0 THEN 0 ELSE SUM(dd.act_inv_time)* 1.0/SUM(dd.act_run_time) END AS issue_inv_loading
|
| 17 |
FROM (
|
18 |
FROM (
|
| 18 |
SELECT
|
19 |
SELECT
|
| Line 85... |
Line 86... |
| 85 |
'wwong','mchua','dmonahan','awehalle','ahulatt','aphoon2','clee1','ahulatt1')
|
86 |
'wwong','mchua','dmonahan','awehalle','ahulatt','aphoon2','clee1','ahulatt1')
|
| 86 |
) dd
|
87 |
) dd
|
| 87 |
GROUP BY dd.test_month,dd.project
|
88 |
GROUP BY dd.test_month,dd.project
|
| 88 |
) rs;
|
89 |
) rs;
|
| 89 |
|
90 |
|
| - |
|
91 |
-- now return result with grans totals row
|
| 90 |
SELECT rs.*,
|
92 |
SELECT rs.*
|
| 91 |
sum_test_rate_pass/ag.project_count as avg_test_rate_pass,
|
- |
|
| 92 |
sum_test_run_efficiency/ag.project_count as avg_test_run_efficiency,
|
- |
|
| 93 |
sum_issue_inv_loading/ag.project_count as avg_issue_inv_loading
|
- |
|
| 94 |
FROM #TD_TEMP rs,
|
93 |
FROM #TD_TEMP rs
|
| - |
|
94 |
UNION
|
| - |
|
95 |
-- get grand totals row and include as PSDC project
|
| 95 |
(SELECT
|
96 |
SELECT
|
| 96 |
test_month,
|
97 |
rs.test_month,
|
| - |
|
98 |
'PSDC' AS project,
|
| 97 |
SUM(test_rate_pass) as sum_test_rate_pass,
|
99 |
SUM(rs.passed_count) AS passed_count,
|
| - |
|
100 |
SUM(rs.run_count) AS run_count,
|
| 98 |
SUM(test_run_efficiency) as sum_test_run_efficiency,
|
101 |
SUM(rs.est_run_total) AS est_run_total,
|
| 99 |
SUM(issue_inv_loading) as sum_issue_inv_loading,
|
102 |
SUM(rs.act_run_total) AS act_run_total,
|
| 100 |
count(*) as project_count
|
103 |
SUM(rs.act_inv_total) AS act_inv_total,
|
| - |
|
104 |
CASE WHEN SUM(rs.run_count) = 0 THEN 0 ELSE SUM(rs.passed_count) * 100.0/SUM(rs.run_count) END AS test_rate_pass,
|
| - |
|
105 |
CASE WHEN SUM(rs.act_run_total) = 0 THEN 0 ELSE SUM(rs.est_run_total)* 1.0/SUM(rs.act_run_total) END AS test_run_efficiency,
|
| - |
|
106 |
CASE WHEN SUM(rs.act_run_total) = 0 THEN 0 ELSE SUM(rs.act_inv_total)* 1.0/SUM(rs.act_run_total) END AS issue_inv_loading
|
| 101 |
FROM #TD_TEMP
|
107 |
FROM #TD_TEMP rs
|
| 102 |
GROUP BY test_month
|
108 |
GROUP BY rs.test_month;
|
| 103 |
) ag
|
- |
|
| 104 |
WHERE
|
- |
|
| 105 |
rs.test_month = ag.test_month
|
- |
|
| 106 |
ORDER BY rs.test_month,rs.project;
|
- |
|
| 107 |
|
109 |
|
| 108 |
DROP TABLE #TD_TEMP;
|
110 |
DROP TABLE #TD_TEMP;
|