Subversion Repositories DevTools

:r ".\queries\SOM_Dashboard_query_init.sql"

IF OBJECT_ID('tempdb..#TD_TEMP') IS NOT NULL DROP TABLE #TD_TEMP;

SELECT * INTO #TD_TEMP

FROM

(SELECT

  dd.test_month,

  dd.project,

  SUM(dd.passed) AS passed_count,

  SUM(dd.run) AS run_count,

  SUM(dd.est_run_time) as est_run_total,

  SUM(dd.act_run_time) as act_run_total,

  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.sub_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,  --est_run_time for TRACS

   tt.ts_user_02,  --est_run_time for non TRACS

   tt.rn_user_01,  --act_run_time

   tt.rn_user_02, --act_inv_time

   tt.ts_user_02_new,--est_run_time, adjusted to be rn_user_01 for NULL values

   tt.rn_user_01_new, --act_run_time, adjusted to be ts_user_02(non-TRACS) or ts_user_01(TRACS) for NULL values

   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 IsNumeric(tt.ts_user_02_new) = 0 THEN 0

        WHEN (CharIndex('.', tt.ts_user_02_new) > 0) THEN CONVERT(float,tt.ts_user_02_new) * 60.0

        ELSE CONVERT(float,tt.ts_user_02_new) END AS est_run_time,

   CASE WHEN IsNumeric(tt.rn_user_01_new) = 0 THEN 0

        WHEN (CharIndex('.', tt.rn_user_01_new) > 0) THEN CONVERT(float,tt.rn_user_01_new) * 60.0

        ELSE CONVERT(float,tt.rn_user_01_new) 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,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

    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,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

    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,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

    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,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

    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,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

    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,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

    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,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

    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 IN ('Passed','Conditional Pass','Failed')     

    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','awehalle','ahulatt','aphoon2','clee1','ahulatt1')

) dd

GROUP BY dd.test_month,dd.project

) rs;

SELECT rs.*,

sum_test_rate_pass/ag.project_count as avg_test_rate_pass,

sum_test_run_efficiency/ag.project_count as avg_test_run_efficiency,

sum_issue_inv_loading/ag.project_count as avg_issue_inv_loading

FROM #TD_TEMP rs,

  (SELECT

    test_month,

    SUM(test_rate_pass) as sum_test_rate_pass,

    SUM(test_run_efficiency) as sum_test_run_efficiency,

    SUM(issue_inv_loading) as sum_issue_inv_loading,

    count(*) as project_count

   FROM #TD_TEMP

   GROUP BY test_month

   ) ag

WHERE

  rs.test_month = ag.test_month

ORDER BY rs.test_month,rs.project;

DROP TABLE #TD_TEMP;