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,
  SUM(dd.act_inv_time) as act_inv_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;

-- now return result with grans totals row
SELECT rs.*
FROM #TD_TEMP rs
UNION
-- get grand totals row and include as PSDC project
SELECT
  rs.test_month,
  'PSDC' AS project,
  SUM(rs.passed_count)  AS passed_count,
  SUM(rs.run_count)     AS run_count,
  SUM(rs.est_run_total) AS est_run_total,
  SUM(rs.act_run_total) AS act_run_total,
  SUM(rs.act_inv_total) AS act_inv_total,
  CASE WHEN SUM(rs.run_count)     = 0 THEN 0 ELSE SUM(rs.passed_count) *  100.0/SUM(rs.run_count)     END AS test_rate_pass,
  CASE WHEN SUM(rs.act_run_total) = 0 THEN 0 ELSE SUM(rs.est_run_total)*    1.0/SUM(rs.act_run_total) END AS test_run_efficiency,
  CASE WHEN SUM(rs.act_run_total) = 0 THEN 0 ELSE SUM(rs.act_inv_total)*    1.0/SUM(rs.act_run_total) END AS issue_inv_loading
FROM #TD_TEMP rs
GROUP BY rs.test_month;

DROP TABLE #TD_TEMP;