| Line 1... |
Line 1... |
| 1 |
:r ".\queries\SOM_Dashboard_query_init.sql"
|
1 |
:r ".\queries\SOM_Dashboard_query_init.sql"
|
| 2 |
|
2 |
|
| - |
|
3 |
IF OBJECT_ID('tempdb..#TD_TEMP') IS NOT NULL DROP TABLE #TD_TEMP;
|
| - |
|
4 |
|
| - |
|
5 |
SELECT * INTO #TD_TEMP
|
| - |
|
6 |
FROM
|
| 3 |
SELECT
|
7 |
(SELECT
|
| 4 |
dd.test_month,
|
8 |
dd.test_month,
|
| 5 |
dd.project,
|
9 |
dd.project,
|
| 6 |
SUM(dd.passed) AS passed_count,
|
10 |
SUM(dd.passed) AS passed_count,
|
| 7 |
SUM(dd.run) AS run_count,
|
11 |
SUM(dd.run) AS run_count,
|
| - |
|
12 |
SUM(dd.est_run_time) as est_run_total,
|
| - |
|
13 |
SUM(dd.act_run_time) as act_run_total,
|
| 8 |
CASE WHEN SUM(dd.run) = 0 THEN 0 ELSE SUM(dd.passed)* 100.0/SUM(dd.run) END AS test_rate_pass,
|
14 |
CASE WHEN SUM(dd.run) = 0 THEN 0 ELSE SUM(dd.passed)* 100.0/SUM(dd.run) END AS test_rate_pass,
|
| 9 |
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,
|
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,
|
| 10 |
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
|
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
|
| 11 |
FROM (
|
17 |
FROM (
|
| 12 |
SELECT
|
18 |
SELECT
|
| Line 77... |
Line 83... |
| 77 |
'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
|
83 |
'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
|
| 78 |
'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
|
84 |
'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
|
| 79 |
'wwong','mchua','dmonahan','awehalle','ahulatt','aphoon2','clee1','ahulatt1')
|
85 |
'wwong','mchua','dmonahan','awehalle','ahulatt','aphoon2','clee1','ahulatt1')
|
| 80 |
) dd
|
86 |
) dd
|
| 81 |
GROUP BY dd.test_month,dd.project
|
87 |
GROUP BY dd.test_month,dd.project
|
| 82 |
ORDER BY dd.test_month,dd.project;
|
- |
|
| 83 |
|
88 |
) rs;
|
| - |
|
89 |
|
| - |
|
90 |
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,
|
| - |
|
95 |
(SELECT
|
| - |
|
96 |
test_month,
|
| - |
|
97 |
SUM(test_rate_pass) as sum_test_rate_pass,
|
| - |
|
98 |
SUM(test_run_efficiency) as sum_test_run_efficiency,
|
| - |
|
99 |
SUM(issue_inv_loading) as sum_issue_inv_loading,
|
| - |
|
100 |
count(*) as project_count
|
| - |
|
101 |
FROM #TD_TEMP
|
| - |
|
102 |
GROUP BY test_month
|
| - |
|
103 |
) ag
|
| - |
|
104 |
WHERE
|
| - |
|
105 |
rs.test_month = ag.test_month
|
| - |
|
106 |
ORDER BY rs.test_month,rs.project;
|
| - |
|
107 |
|
| - |
|
108 |
DROP TABLE #TD_TEMP;
|
| - |
|
109 |
|