Subversion Repositories DevTools

Rev

Blame | Last modification | View Log | RSS feed 

:r ".\queries\SOM_Dashboard_query_init.sql"

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

SELECT * INTO #CQ_TEMP
FROM
(SELECT
       df.defect_month,
       df.project,
       SUM(df.defect_critical) AS defect_severity_critical_count,
       SUM(df.defect_important) AS defect_severity_important_count,
       SUM(df.defect_routine) AS defect_severity_routine_count,
       SUM(df.fixed) AS defect_fixed_count,
       SUM(df.raised) AS defect_raised_count,
       CASE WHEN SUM(df.defect_critical + df.defect_important + df.defect_routine) = 0 THEN 0 ELSE SUM(df.defect_critical) * 100.0 / SUM(df.defect_critical + df.defect_important + df.defect_routine) END AS defect_severity_critical,
       CASE WHEN SUM(df.defect_critical + df.defect_important + df.defect_routine) = 0 THEN 0 ELSE SUM(df.defect_important) * 100.0 / SUM(df.defect_critical + df.defect_important + df.defect_routine) END AS defect_severity_important,
       CASE WHEN SUM(df.defect_critical + df.defect_important + df.defect_routine) = 0 THEN 0 ELSE SUM(df.defect_routine) * 100.0 / SUM(df.defect_critical + df.defect_important + df.defect_routine) END AS defect_severity_routine,
       CASE WHEN SUM(df.raised) = 0 THEN 0 ELSE SUM(df.fixed) * 1.0 /SUM(df.raised) END AS defect_ratio
FROM
  (
  -- get submitted defects
  SELECT  
       si.issue_id,
       DATEADD(mm,(YEAR(DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date))-1900)* 12 + MONTH(DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date)) - 1, 0)AS defect_month, -- truncate to year,month
       CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
            WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
            ELSE si.project
       END AS project,
       si.project as sub_project,
       CASE WHEN ((si.severity IS NULL) AND (si.priority = '1-Critical')) OR (si.severity = '1-Critical') THEN 1 ELSE 0 END AS defect_critical, 
       CASE WHEN ((si.severity IS NULL) AND (si.priority = '2-Important')) OR (si.severity = '2-Important') THEN 1 ELSE 0 END AS defect_important, 
       CASE WHEN ((si.severity IS NULL) AND (si.priority  IN ('3-Routine','4-Low'))) OR (si.severity  IN ('3-Routine','4-Low')) THEN 1 ELSE 0 END AS defect_routine, 
       0 AS fixed,
       1 AS raised,
       DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date) AS defect_datetime, 
       sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
   FROM
     CQ_DEVI.admin.software_issue si,
     CQ_DEVI.admin.statedef sd,
     CQ_DEVI.admin.users us
   WHERE
          si.state = sd.id
      AND si.submitter = us.dbid
      --AND issue_id IN (57889,57987,61073,65474)
      --AND si.issue_id IN (68144)
      --AND si.project = 'Seattle'
      -- filter on date
      --AND si.submit_date >= '20110801' AND si.submit_date  < '20111101'
      -- filter on project
      AND si.project IN ('Seattle','VasTraffik 1','Vas Traffik 2/3','Stagecoach Bus Hops','Stagecoach South West','TRACS')
      -- filter on defect type
      AND si.issue_type IN ('Defect','FQT defect','Integration defect','Test Failure','Documentation')
      -- filter on submitter
      AND (
          (us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','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'))
          OR
          (us.login_name IN ('fpatel') AND (DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date) < '20120130'))
      )

      --AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
      --                    'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
      --                    'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
      --                    'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')
  UNION 
  -- get closed defects
  SELECT 
       si.issue_id,
       DATEADD(mm,(YEAR(DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.close_date))-1900)* 12 + MONTH(DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.close_date)) - 1, 0)AS defect_month, -- truncate to year,month
       CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
            WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
            ELSE si.project
       END AS project,
       si.project as sub_project,
       0 AS defect_critical, 
       0 AS defect_important, 
       0 AS defect_routine, 
       1 AS fixed,
       0 AS raised,
       DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.close_date) AS defect_datetime,        
       sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
   FROM
     CQ_DEVI.admin.software_issue si,
     CQ_DEVI.admin.statedef sd,
     CQ_DEVI.admin.users us
   WHERE
          si.state = sd.id
      AND si.submitter = us.dbid
      AND sd.name = 'Closed'
      --AND si.issue_id IN (68144)
      --AND issue_id IN (57889,57987,61073,65474)
       --AND si.project = 'Seattle'
      -- filter on date
      --AND si.close_date >= '20110801' AND si.close_date  < '20111101'
      -- filter on project
      AND si.project IN ('Seattle','VasTraffik 1','Vas Traffik 2/3','Stagecoach Bus Hops','Stagecoach South West','TRACS')
      -- filter on defect type
      AND si.issue_type IN ('Defect','FQT defect','Integration defect','Test Failure','Documentation')
      -- filter on submitter
      AND us.login_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')

       --AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
      --                    'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
      --                    'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
      --                    'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')

  UNION
  -- JIRA
  -- get submitted defects
  SELECT  
       si.id as issue_id,
       DATEADD(mm,(YEAR(created)-1900)* 12 + MONTH(created) - 1, 0)AS defect_month, -- truncate to year,month
       CASE WHEN pr.pname IN ('SLS Iteration Plan') THEN 'Stockholm'
            ELSE pr.pname
       END AS project,
       pr.pname as sub_project,
       CASE WHEN (sp.pname IN ('Critical','Blocker')) THEN 1 ELSE 0 END AS defect_critical, 
       CASE WHEN (sp.pname = 'Major') THEN 1 ELSE 0 END AS defect_important, 
       CASE WHEN (sp.pname  IN ('Minor','Trivial')) THEN 1 ELSE 0 END AS defect_routine, 
       0 AS fixed,
       1 AS raised,
       si.created AS defect_datetime, 
       sd.pname as issue_state,si.created as submit_date,si.updated as close_date,si.reporter as login_name, null as severity ,sp.pname as priority,st.pname as issue_type
   FROM
     JIRA.jirauser.jiraissue si,
     JIRA.jirauser.issuestatus sd,
     JIRA.jirauser.project pr,
     JIRA.jirauser.issuetype st,
     JIRA.jirauser.priority sp
   WHERE
          si.issuestatus   = sd.id
      AND si.project = pr.id
      AND si.issuetype = st.id
      AND si.priority = sp.id
      -- filter on date
      AND si.created >= '20090101'
      -- filter on project
      AND pr.pname IN ('SLS Iteration Plan','Cape Town')
      -- filter on defect type
      AND st.pname IN('Bug','Documentation')
      -- filter on submitter
      AND si.reporter 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')
  UNION 
  -- get closed defects
  SELECT 
       si.id as issue_id,
       DATEADD(mm,(YEAR(si.updated)-1900)* 12 + MONTH(si.updated) - 1, 0)AS defect_month, -- truncate to year,month
       CASE WHEN pr.pname IN ('SLS Iteration Plan') THEN 'Stockholm'
            ELSE pr.pname
       END AS project,
       pr.pname as sub_project,
       0 AS defect_critical, 
       0 AS defect_important, 
       0 AS defect_routine, 
       1 AS fixed,
       0 AS raised,
       si.updated AS defect_datetime,
       sd.pname as issue_state,si.created as submit_date,si.updated as close_date,si.reporter as login_name, null as severity ,sp.pname as priority,st.pname as issue_type
   FROM
     JIRA.jirauser.jiraissue si,
     JIRA.jirauser.issuestatus sd,
     JIRA.jirauser.project pr,
     JIRA.jirauser.issuetype st,
     JIRA.jirauser.priority sp
   WHERE
          si.issuestatus   = sd.id
      AND si.project = pr.id
      AND si.issuetype = st.id
      AND si.priority = sp.id
      AND sd.pname = 'Closed'
      -- filter on date
      AND si.updated >= '20090101'
      -- filter on project
      AND pr.pname IN ('SLS Iteration Plan','Cape Town')
      -- filter on defect type
      AND st.pname IN('Bug','Documentation')
      -- filter on submitter
      AND si.reporter 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')
  ) df
where
    df.defect_datetime >= @start_date AND df.defect_datetime < @end_date
GROUP BY df.defect_month,df.project
) rs;

-- now return result with grans totals row
SELECT rs.*
FROM #CQ_TEMP rs
UNION
-- get grand totals row and include as PSDC project
SELECT
  rs.defect_month,
  'PSDC' as project,
  SUM(rs.defect_severity_critical_count)  AS defect_severity_critical_count,
  SUM(rs.defect_severity_important_count) AS defect_severity_important_count,
  SUM(rs.defect_severity_routine_count)   AS defect_severity_routine_count,
  SUM(rs.defect_fixed_count)  AS defect_fixed_count,
  SUM(rs.defect_raised_count) AS defect_raised_count,
  CASE WHEN SUM(rs.defect_severity_critical_count + rs.defect_severity_important_count + rs.defect_severity_routine_count)          = 0 THEN 0 ELSE SUM(rs.defect_severity_critical_count)  * 100.0/ SUM(rs.defect_severity_critical_count + rs.defect_severity_important_count + rs.defect_severity_routine_count)  END AS defect_severity_critical,
  CASE WHEN SUM(rs.defect_severity_critical_count + rs.defect_severity_important_count + rs.defect_severity_routine_count)          = 0 THEN 0 ELSE SUM(rs.defect_severity_important_count) * 100.0/ SUM(rs.defect_severity_critical_count + rs.defect_severity_important_count + rs.defect_severity_routine_count)  END AS defect_severity_important,
  CASE WHEN SUM(rs.defect_severity_critical_count + rs.defect_severity_important_count + rs.defect_severity_routine_count)          = 0 THEN 0 ELSE SUM(rs.defect_severity_routine_count)   * 100.0/ SUM(rs.defect_severity_critical_count + rs.defect_severity_important_count + rs.defect_severity_routine_count)  END AS defect_severity_routine,
  CASE WHEN SUM(rs.defect_raised_count) = 0 THEN 0 ELSE SUM(rs.defect_fixed_count) * 1.0  / SUM(rs.defect_raised_count) END AS defect_ratio
FROM #CQ_TEMP rs
GROUP BY rs.defect_month;
--ORDER BY rs.defect_month,rs.project;

DROP TABLE #CQ_TEMP;