| 2195 |
brianf |
1 |
:r ".\queries\SOM_Dashboard_query_init.sql"
|
|
|
2 |
|
|
|
3 |
SELECT
|
|
|
4 |
dd.test_month,
|
|
|
5 |
dd.project,
|
|
|
6 |
SUM(dd.passed) AS passed_count,
|
|
|
7 |
SUM(dd.run) AS run_count,
|
|
|
8 |
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,
|
|
|
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
|
|
|
11 |
FROM (
|
|
|
12 |
SELECT
|
|
|
13 |
DATEADD(mm,(YEAR(tt.rn_execution_date)-1900)* 12 + MONTH(tt.rn_execution_date) - 1, 0)AS test_month, -- truncate to year,month
|
|
|
14 |
tt.project,
|
|
|
15 |
tt.rn_status,
|
|
|
16 |
tt.rn_execution_date,
|
|
|
17 |
tt.cy_cycle_id,tt.rn_run_id,tt.ts_test_id,
|
|
|
18 |
tt.rn_tester_name,
|
|
|
19 |
tt.ts_user_01,tt.ts_user_02,tt.rn_user_01,tt.rn_user_02,
|
|
|
20 |
CASE WHEN (IsNumeric(tt.ts_user_01) = 1) AND (CharIndex('.', tt.ts_user_01) = 0) THEN 1 ELSE 0 END AS IsInt_TU1,
|
|
|
21 |
CASE WHEN (IsNumeric(tt.ts_user_02) = 1) AND (CharIndex('.', tt.ts_user_02) = 0) THEN 1 ELSE 0 END AS IsInt_TU2,
|
|
|
22 |
CASE WHEN (IsNumeric(tt.ts_user_01) = 1) AND (CharIndex('.', tt.rn_user_01) = 0) THEN 1 ELSE 0 END AS IsInt_RU1,
|
|
|
23 |
CASE WHEN (IsNumeric(tt.ts_user_02) = 1) AND (CharIndex('.', tt.rn_user_02) = 0) THEN 1 ELSE 0 END AS IsInt_RU2,
|
|
|
24 |
CASE WHEN tt.rn_status = 'Passed' THEN 1 ELSE 0 END AS Passed,
|
|
|
25 |
1 AS Run,
|
|
|
26 |
CASE WHEN tt.sub_project IN ('TRACS') THEN
|
|
|
27 |
CASE WHEN IsNumeric(tt.ts_user_01) = 0 THEN 0
|
|
|
28 |
WHEN (CharIndex('.', tt.ts_user_01) > 0) THEN CONVERT(float,tt.ts_user_01) * 60.0
|
|
|
29 |
ELSE CONVERT(float,tt.ts_user_01) END
|
|
|
30 |
ELSE
|
|
|
31 |
CASE WHEN IsNumeric(tt.ts_user_02) = 0 THEN 0
|
|
|
32 |
WHEN (CharIndex('.', tt.ts_user_02) > 0) THEN CONVERT(float,tt.ts_user_02) * 60.0
|
|
|
33 |
ELSE CONVERT(float,tt.ts_user_02) END
|
|
|
34 |
END AS est_run_time,
|
|
|
35 |
CASE WHEN IsNumeric(tt.rn_user_01) = 0 THEN 0
|
|
|
36 |
WHEN (CONVERT(float,tt.rn_user_01) > 1000) THEN 0
|
|
|
37 |
WHEN (CharIndex('.', tt.rn_user_01) > 0) THEN CONVERT(float,tt.rn_user_01) * 60.0
|
|
|
38 |
ELSE CONVERT(float,tt.rn_user_01) END AS act_run_time,
|
|
|
39 |
CASE WHEN IsNumeric(tt.rn_user_02) = 0 THEN 0
|
|
|
40 |
WHEN (CONVERT(float,tt.rn_user_02) > 1000) THEN 0
|
|
|
41 |
WHEN (CharIndex('.', tt.rn_user_02) > 0) THEN CONVERT(float,tt.rn_user_02) * 60.0
|
|
|
42 |
ELSE CONVERT(float,tt.rn_user_02) END AS act_inv_time
|
|
|
43 |
FROM (
|
|
|
44 |
SELECT 'Seattle' AS project,'Seattle' as sub_project,rn.rn_status,rn.rn_execution_date,cy.cy_cycle_id,rn.rn_run_id,ts.ts_test_id,rn.rn_tester_name,ts.ts_user_01,ts.ts_user_02,rn.rn_user_01,rn.rn_user_02
|
|
|
45 |
FROM erg_seattle_phase2_db.td.cycle cy, erg_seattle_phase2_db.td.run rn, erg_seattle_phase2_db.td.test ts
|
|
|
46 |
WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
|
|
|
47 |
UNION
|
|
|
48 |
SELECT 'Stockholm' AS project,'Stockholm' as sub_project,rn.rn_status,rn.rn_execution_date,cy.cy_cycle_id,rn.rn_run_id,ts.ts_test_id,rn.rn_tester_name,ts.ts_user_01,ts.ts_user_02,rn.rn_user_01,rn.rn_user_02
|
|
|
49 |
FROM erg_stockholm_db.td.cycle cy, erg_stockholm_db.td.run rn, erg_stockholm_db.td.test ts
|
|
|
50 |
WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
|
|
|
51 |
UNION
|
|
|
52 |
SELECT 'Capetown' AS project,'Capetown' as sub_project,rn.rn_status,rn.rn_execution_date,cy.cy_cycle_id,rn.rn_run_id,ts.ts_test_id,rn.rn_tester_name,ts.ts_user_01,ts.ts_user_02,rn.rn_user_01,rn.rn_user_02
|
|
|
53 |
FROM erg_capetown_db.td.cycle cy, erg_capetown_db.td.run rn, erg_capetown_db.td.test ts
|
|
|
54 |
WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
|
|
|
55 |
UNION
|
|
|
56 |
SELECT 'UK' AS project,'ITSO' as sub_project,rn.rn_status,rn.rn_execution_date,cy.cy_cycle_id,rn.rn_run_id,ts.ts_test_id,rn.rn_tester_name,ts.ts_user_01,ts.ts_user_02,rn.rn_user_01,rn.rn_user_02
|
|
|
57 |
FROM erg_itso_db.td.cycle cy, erg_itso_db.td.run rn, erg_itso_db.td.test ts
|
|
|
58 |
WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
|
|
|
59 |
UNION
|
|
|
60 |
SELECT 'UK' AS project,'SSW' as sub_project,rn.rn_status,rn.rn_execution_date,cy.cy_cycle_id,rn.rn_run_id,ts.ts_test_id,rn.rn_tester_name,ts.ts_user_01,ts.ts_user_02,rn.rn_user_01,rn.rn_user_02
|
|
|
61 |
FROM erg_soutwesttrains_db.td.cycle cy, erg_soutwesttrains_db.td.run rn, erg_soutwesttrains_db.td.test ts
|
|
|
62 |
WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
|
|
|
63 |
UNION
|
|
|
64 |
SELECT 'UK' AS project,'TRACS' as sub_project,rn.rn_status,rn.rn_execution_date,cy.cy_cycle_id,rn.rn_run_id,ts.ts_test_id,rn.rn_tester_name,ts.ts_user_01,ts.ts_user_02,rn.rn_user_01,rn.rn_user_02
|
|
|
65 |
FROM erg_tracs_db.td.cycle cy, erg_tracs_db.td.run rn, erg_tracs_db.td.test ts
|
|
|
66 |
WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
|
|
|
67 |
UNION
|
|
|
68 |
SELECT 'Vastraffik' AS project,'Vastraffik' as sub_project,rn.rn_status,rn.rn_execution_date,cy.cy_cycle_id,rn.rn_run_id,ts.ts_test_id,rn.rn_tester_name,ts.ts_user_01,ts.ts_user_02,rn.rn_user_01,rn.rn_user_02
|
|
|
69 |
FROM erg_vastraffik_db.td.cycle cy, erg_vastraffik_db.td.run rn, erg_vastraffik_db.td.test ts
|
|
|
70 |
WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
|
|
|
71 |
) tt
|
|
|
72 |
where
|
|
|
73 |
tt.rn_status NOT IN ('Not Run','Not Completed')
|
|
|
74 |
and tt.rn_execution_date >= @start_date AND tt.rn_execution_date < @end_date
|
|
|
75 |
--and tt.rn_execution_date >= '20111001' AND tt.rn_execution_date < '20111101'
|
|
|
76 |
and tt.rn_tester_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
|
|
|
77 |
'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
|
|
|
78 |
'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
|
|
|
79 |
'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
|
|
|
80 |
'wwong','mchua','dmonahan')
|
|
|
81 |
) dd
|
|
|
82 |
GROUP BY dd.test_month,dd.project
|
|
|
83 |
ORDER BY dd.test_month,dd.project;
|