Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2195 brianf 1 
:r ".\queries\SOM_Dashboard_query_init.sql"
2 
 
3 
SELECT df.defect_month,
4 
       df.project,
5 
       SUM(df.defect_critical) AS defect_severity_critical_count,
6 
       SUM(df.defect_important) AS defect_severity_important_count,
7 
       SUM(df.defect_routine) AS defect_severity_routine_count,
8 
       SUM(df.fixed) AS defect_fixed_count,
9 
       SUM(df.raised) AS defect_raised_count,
10 
       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,
11 
       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,
12 
       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,
13 
       CASE WHEN SUM(df.raised) = 0 THEN 0 ELSE SUM(df.fixed) * 1.0 /SUM(df.raised) END AS defect_ratio
14 
FROM
15 
  (
16 
  -- get submitted defects
17 
  SELECT
18 
       si.id as issue_id,
19 
       DATEADD(mm,(YEAR(created)-1900)* 12 + MONTH(created) - 1, 0)AS defect_month, -- truncate to year,month
20 
       CASE WHEN pr.pname IN ('SLS Iteration Plan') THEN 'Stockholm'
21 
            ELSE pr.pname
22 
       END AS project,
23 
       pr.pname as sub_project,
24 
       CASE WHEN (sp.pname IN ('Critical','Blocker')) THEN 1 ELSE 0 END AS defect_critical,
25 
       CASE WHEN (sp.pname = 'Major') THEN 1 ELSE 0 END AS defect_important,
26 
       CASE WHEN (sp.pname  IN ('Minor','Trivial')) THEN 1 ELSE 0 END AS defect_routine,
27 
 
28 
       1 AS raised,
29 
       si.created AS defect_datetime,
30 
       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
31 
   FROM
32 
     JIRA.jirauser.jiraissue si,
33 
     JIRA.jirauser.issuestatus sd,
34 
     JIRA.jirauser.project pr,
35 
     JIRA.jirauser.issuetype st,
36 
     JIRA.jirauser.priority sp
37 
   WHERE
38 
          si.issuestatus   = sd.id
39 
      AND si.project = pr.id
40 
      AND si.issuetype = st.id
41 
      AND si.priority = sp.id
42 
      -- filter on date
43 
      AND si.created >= '20090101'
44 
      -- filter on project
45 
      AND pr.pname IN ('SLS Iteration Plan','Cape Town')
46 
      -- filter on defect type
47 
      AND st.pname IN('Bug','Documentation')
48 
      -- filter on submitter
49 
      AND si.reporter IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
50 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
51 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
52 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
2197 brianf 53 
                          'wwong','mchua','dmonahan','awehalle')
2195 brianf 54 
  UNION
55 
  -- get closed defects
56 
  SELECT
57 
       si.id as issue_id,
58 
       DATEADD(mm,(YEAR(si.updated)-1900)* 12 + MONTH(si.updated) - 1, 0)AS defect_month, -- truncate to year,month
59 
       CASE WHEN pr.pname IN ('SLS Iteration Plan') THEN 'Stockholm'
60 
            ELSE pr.pname
61 
       END AS project,
62 
       pr.pname as sub_project,
63 
 
64 
 
65 
 
66 
       1 AS fixed,
67 
 
2199 brianf 68 
       si.updated AS defect_datetime,
2195 brianf 69 
       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
70 
   FROM
71 
     JIRA.jirauser.jiraissue si,
72 
     JIRA.jirauser.issuestatus sd,
73 
     JIRA.jirauser.project pr,
74 
     JIRA.jirauser.issuetype st,
75 
     JIRA.jirauser.priority sp
76 
   WHERE
77 
          si.issuestatus   = sd.id
78 
      AND si.project = pr.id
79 
      AND si.issuetype = st.id
80 
      AND si.priority = sp.id
81 
      AND sd.pname = 'Closed'
82 
      -- filter on date
83 
      AND si.updated >= '20090101'
84 
      -- filter on project
85 
      AND pr.pname IN ('SLS Iteration Plan','Cape Town')
86 
      -- filter on defect type
87 
      AND st.pname IN('Bug','Documentation')
88 
      -- filter on submitter
89 
      AND si.reporter IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
90 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
91 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
92 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
2197 brianf 93 
                          'wwong','mchua','dmonahan','awehalle')
2195 brianf 94 
   ) df
95 
   where
96 
    df.defect_datetime >= @start_date AND df.defect_datetime < @end_date
97 
GROUP BY df.defect_month,df.project;