Subversion Repositories DevTools

:r ".\queries\SOM_Dashboard_query_init.sql"

SELECT
  dd.test_month,
  dd.project,
  SUM(dd.passed) AS passed_count,
  SUM(dd.run) AS run_count,
  CASE WHEN SUM(dd.run) = 0 THEN 0 ELSE SUM(dd.passed)* 100.0/SUM(dd.run) END AS test_rate_pass,
  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,
  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
FROM (
  SELECT
   DATEADD(mm,(YEAR(tt.rn_execution_date)-1900)* 12 + MONTH(tt.rn_execution_date) - 1, 0)AS test_month, -- truncate to year,month
   tt.project,
   tt.rn_status,
   tt.rn_execution_date,
   tt.cy_cycle_id,tt.rn_run_id,tt.ts_test_id,
   tt.rn_tester_name,
   tt.ts_user_01,tt.ts_user_02,tt.rn_user_01,tt.rn_user_02,
   CASE WHEN (IsNumeric(tt.ts_user_01) = 1) AND (CharIndex('.', tt.ts_user_01) = 0) THEN 1 ELSE 0 END AS IsInt_TU1,
   CASE WHEN (IsNumeric(tt.ts_user_02) = 1) AND (CharIndex('.', tt.ts_user_02) = 0) THEN 1 ELSE 0 END AS IsInt_TU2,
   CASE WHEN (IsNumeric(tt.ts_user_01) = 1) AND (CharIndex('.', tt.rn_user_01) = 0) THEN 1 ELSE 0 END AS IsInt_RU1,
   CASE WHEN (IsNumeric(tt.ts_user_02) = 1) AND (CharIndex('.', tt.rn_user_02) = 0) THEN 1 ELSE 0 END AS IsInt_RU2,
   CASE WHEN tt.rn_status = 'Passed' THEN 1 ELSE 0 END AS Passed,
   1 AS Run,
   CASE WHEN tt.sub_project IN ('TRACS') THEN
       CASE WHEN IsNumeric(tt.ts_user_01) = 0 THEN 0
            WHEN (CharIndex('.', tt.ts_user_01) > 0) THEN CONVERT(float,tt.ts_user_01) * 60.0
            ELSE CONVERT(float,tt.ts_user_01) END
   ELSE     
       CASE WHEN IsNumeric(tt.ts_user_02) = 0 THEN 0
            WHEN (CharIndex('.', tt.ts_user_02) > 0) THEN CONVERT(float,tt.ts_user_02) * 60.0
            ELSE CONVERT(float,tt.ts_user_02) END
   END AS est_run_time,  
   CASE WHEN IsNumeric(tt.rn_user_01) = 0 THEN 0
        WHEN (CONVERT(float,tt.rn_user_01) > 1000) THEN 0
        WHEN (CharIndex('.', tt.rn_user_01) > 0) THEN CONVERT(float,tt.rn_user_01) * 60.0
        ELSE CONVERT(float,tt.rn_user_01) END AS act_run_time,
   CASE WHEN IsNumeric(tt.rn_user_02) = 0 THEN 0
        WHEN (CONVERT(float,tt.rn_user_02) > 1000) THEN 0
        WHEN (CharIndex('.', tt.rn_user_02) > 0) THEN CONVERT(float,tt.rn_user_02) * 60.0
        ELSE CONVERT(float,tt.rn_user_02) END AS act_inv_time
  FROM (
    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
    FROM  erg_seattle_phase2_db.td.cycle cy, erg_seattle_phase2_db.td.run rn, erg_seattle_phase2_db.td.test ts 
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
    UNION
    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
    FROM  erg_stockholm_db.td.cycle cy, erg_stockholm_db.td.run rn, erg_stockholm_db.td.test ts
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
    UNION
    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
    FROM  erg_capetown_db.td.cycle cy, erg_capetown_db.td.run rn, erg_capetown_db.td.test ts
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
    UNION
    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
    FROM  erg_itso_db.td.cycle cy, erg_itso_db.td.run rn, erg_itso_db.td.test ts
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
    UNION
    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
    FROM  erg_soutwesttrains_db.td.cycle cy, erg_soutwesttrains_db.td.run rn, erg_soutwesttrains_db.td.test ts
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
    UNION
    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
    FROM  erg_tracs_db.td.cycle cy, erg_tracs_db.td.run rn, erg_tracs_db.td.test ts
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
    UNION
    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
    FROM  erg_vastraffik_db.td.cycle cy, erg_vastraffik_db.td.run rn, erg_vastraffik_db.td.test ts
    WHERE cy.cy_cycle_id = rn.rn_cycle_id AND rn.rn_test_id = ts.ts_test_id AND rn.rn_execution_date >= '20090101'
     ) tt
where
    tt.rn_status NOT IN ('Not Run','Not Completed')
    and tt.rn_execution_date >= @start_date AND tt.rn_execution_date < @end_date
    --and tt.rn_execution_date >= '20111001' AND tt.rn_execution_date < '20111101'
    and tt.rn_tester_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
                          'wwong','mchua','dmonahan')
) dd
GROUP BY dd.test_month,dd.project
ORDER BY dd.test_month,dd.project;