Subversion Repositories DevTools

Rev

Rev 2199 | Rev 2207 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
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,
2199 brianf 15 
   tt.sub_project,
2195 brianf 16 
   tt.rn_status,
17 
   tt.rn_execution_date,
18 
   tt.cy_cycle_id,tt.rn_run_id,tt.ts_test_id,
19 
   tt.rn_tester_name,
2199 brianf 20 
   tt.ts_user_01,  --est_run_time for TRACS
21 
   tt.ts_user_02,  --est_run_time for non TRACS
22 
   tt.rn_user_01,  --act_run_time
23 
   tt.rn_user_02, --act_inv_time
24 
   tt.ts_user_02_new,--est_run_time, adjusted to be rn_user_01 for NULL values
25 
   tt.rn_user_01_new, --act_run_time, adjusted to be ts_user_02(non-TRACS) or ts_user_01(TRACS) for NULL values
2195 brianf 26 
   CASE WHEN (IsNumeric(tt.ts_user_01) = 1) AND (CharIndex('.', tt.ts_user_01) = 0) THEN 1 ELSE 0 END AS IsInt_TU1,
27 
   CASE WHEN (IsNumeric(tt.ts_user_02) = 1) AND (CharIndex('.', tt.ts_user_02) = 0) THEN 1 ELSE 0 END AS IsInt_TU2,
28 
   CASE WHEN (IsNumeric(tt.ts_user_01) = 1) AND (CharIndex('.', tt.rn_user_01) = 0) THEN 1 ELSE 0 END AS IsInt_RU1,
29 
   CASE WHEN (IsNumeric(tt.ts_user_02) = 1) AND (CharIndex('.', tt.rn_user_02) = 0) THEN 1 ELSE 0 END AS IsInt_RU2,
30 
   CASE WHEN tt.rn_status = 'Passed' THEN 1 ELSE 0 END AS Passed,
31 
   1 AS Run,
2199 brianf 32 
   CASE WHEN IsNumeric(tt.ts_user_02_new) = 0 THEN 0
33 
        WHEN (CharIndex('.', tt.ts_user_02_new) > 0) THEN CONVERT(float,tt.ts_user_02_new) * 60.0
34 
        ELSE CONVERT(float,tt.ts_user_02_new) END AS est_run_time,
35 
   CASE WHEN IsNumeric(tt.rn_user_01_new) = 0 THEN 0
36 
        WHEN (CharIndex('.', tt.rn_user_01_new) > 0) THEN CONVERT(float,tt.rn_user_01_new) * 60.0
37 
        ELSE CONVERT(float,tt.rn_user_01_new) END AS act_run_time,
2195 brianf 38 
   CASE WHEN IsNumeric(tt.rn_user_02) = 0 THEN 0
39 
        WHEN (CONVERT(float,tt.rn_user_02) > 1000) THEN 0
40 
        WHEN (CharIndex('.', tt.rn_user_02) > 0) THEN CONVERT(float,tt.rn_user_02) * 60.0
41 
        ELSE CONVERT(float,tt.rn_user_02) END AS act_inv_time
42 
  FROM (
2199 brianf 43 
    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,ISNULL(ts.ts_user_02,rn.rn_user_01) AS ts_user_02_new,ISNULL(rn.rn_user_01,ts.ts_user_02) AS rn_user_01_new
2195 brianf 44 
    FROM  erg_seattle_phase2_db.td.cycle cy, erg_seattle_phase2_db.td.run rn, erg_seattle_phase2_db.td.test ts
45 
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
46 
    UNION
2199 brianf 47 
    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,ISNULL(ts.ts_user_02,rn.rn_user_01) AS ts_user_02_new,ISNULL(rn.rn_user_01,ts.ts_user_02) AS rn_user_01_new
2195 brianf 48 
    FROM  erg_stockholm_db.td.cycle cy, erg_stockholm_db.td.run rn, erg_stockholm_db.td.test ts
49 
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
50 
    UNION
2199 brianf 51 
    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,ISNULL(ts.ts_user_02,rn.rn_user_01) AS ts_user_02_new,ISNULL(rn.rn_user_01,ts.ts_user_02) AS rn_user_01_new
2195 brianf 52 
    FROM  erg_capetown_db.td.cycle cy, erg_capetown_db.td.run rn, erg_capetown_db.td.test ts
53 
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
54 
    UNION
2199 brianf 55 
    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,ISNULL(ts.ts_user_02,rn.rn_user_01) AS ts_user_02_new,ISNULL(rn.rn_user_01,ts.ts_user_02) AS rn_user_01_new
2195 brianf 56 
    FROM  erg_itso_db.td.cycle cy, erg_itso_db.td.run rn, erg_itso_db.td.test ts
57 
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
58 
    UNION
2199 brianf 59 
    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,ISNULL(ts.ts_user_02,rn.rn_user_01) AS ts_user_02_new,ISNULL(rn.rn_user_01,ts.ts_user_02) AS rn_user_01_new
2195 brianf 60 
    FROM  erg_soutwesttrains_db.td.cycle cy, erg_soutwesttrains_db.td.run rn, erg_soutwesttrains_db.td.test ts
61 
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
62 
    UNION
2199 brianf 63 
    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,ISNULL(ts.ts_user_01,rn.rn_user_01) AS ts_user_02_new,ISNULL(rn.rn_user_01,ts.ts_user_01) AS rn_user_01_new
2195 brianf 64 
    FROM  erg_tracs_db.td.cycle cy, erg_tracs_db.td.run rn, erg_tracs_db.td.test ts
65 
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
66 
    UNION
2199 brianf 67 
    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,ISNULL(ts.ts_user_02,rn.rn_user_01) AS ts_user_02_new,ISNULL(rn.rn_user_01,ts.ts_user_02) AS rn_user_01_new
2195 brianf 68 
    FROM  erg_vastraffik_db.td.cycle cy, erg_vastraffik_db.td.run rn, erg_vastraffik_db.td.test ts
69 
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
70 
     ) tt
71 
where
2199 brianf 72 
    tt.rn_status IN ('Passed','Conditional Pass','Failed')
2195 brianf 73 
    and tt.rn_execution_date >= @start_date AND tt.rn_execution_date < @end_date
74 
    --and tt.rn_execution_date >= '20111001' AND tt.rn_execution_date < '20111101'
75 
    and tt.rn_tester_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
76 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
77 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
78 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
2201 brianf 79 
                          'wwong','mchua','dmonahan','awehalle','ahulatt','aphoon2','clee1','ahulatt1')
2195 brianf 80 
) dd
81 
GROUP BY dd.test_month,dd.project
82 
ORDER BY dd.test_month,dd.project;