Subversion Repositories DevTools

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2205 brianf 1 
:r ".\queries\SOM_Dashboard_query_init.sql"
2195 brianf 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.issue_id,
2197 brianf 19 
       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
2195 brianf 20 
       CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
21 
            WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
22 
            ELSE si.project
23 
       END AS project,
24 
       si.project as sub_project,
25 
       CASE WHEN ((si.severity IS NULL) AND (si.priority = '1-Critical')) OR (si.severity = '1-Critical') THEN 1 ELSE 0 END AS defect_critical,
26 
       CASE WHEN ((si.severity IS NULL) AND (si.priority = '2-Important')) OR (si.severity = '2-Important') THEN 1 ELSE 0 END AS defect_important,
27 
       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,
28 
 
29 
       1 AS raised,
2197 brianf 30 
       DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date) AS defect_datetime,
2195 brianf 31 
       sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
32 
   FROM
33 
     CQ_DEVI.admin.software_issue si,
34 
     CQ_DEVI.admin.statedef sd,
35 
     CQ_DEVI.admin.users us
36 
   WHERE
37 
          si.state = sd.id
38 
      AND si.submitter = us.dbid
39 
      --AND issue_id IN (57889,57987,61073,65474)
40 
      --AND si.issue_id IN (68144)
41 
      --AND si.project = 'Seattle'
42 
      -- filter on date
43 
      --AND si.submit_date >= '20110801' AND si.submit_date  < '20111101'
44 
      -- filter on project
45 
      AND si.project IN ('Seattle','VasTraffik 1','Vas Traffik 2/3','Stagecoach Bus Hops','Stagecoach South West','TRACS')
46 
      -- filter on defect type
47 
      AND si.issue_type IN ('Defect','FQT defect','Integration defect','Test Failure','Documentation')
48 
      -- filter on submitter
2203 brianf 49 
      AND (
50 
          (us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','lguntur',
2195 brianf 51 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
52 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
53 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
2203 brianf 54 
                          'wwong','mchua','dmonahan','awehalle'))
55 
          OR
56 
          (us.login_name IN ('fpatel') AND (DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date) < '20120130'))
57 
      )
58 
 
2195 brianf 59 
      --AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
60 
      --                    'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
61 
      --                    'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
62 
      --                    'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')
63 
  UNION
64 
  -- get closed defects
65 
  SELECT
66 
       si.issue_id,
2197 brianf 67 
       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
2195 brianf 68 
       CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
69 
            WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
70 
            ELSE si.project
71 
       END AS project,
72 
       si.project as sub_project,
73 
 
74 
 
75 
 
76 
       1 AS fixed,
77 
 
2197 brianf 78 
       DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.close_date) AS defect_datetime,
2195 brianf 79 
       sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
80 
   FROM
81 
     CQ_DEVI.admin.software_issue si,
82 
     CQ_DEVI.admin.statedef sd,
83 
     CQ_DEVI.admin.users us
84 
   WHERE
85 
          si.state = sd.id
86 
      AND si.submitter = us.dbid
87 
      AND sd.name = 'Closed'
88 
      --AND si.issue_id IN (68144)
89 
      --AND issue_id IN (57889,57987,61073,65474)
90 
       --AND si.project = 'Seattle'
91 
      -- filter on date
92 
      --AND si.close_date >= '20110801' AND si.close_date  < '20111101'
93 
      -- filter on project
94 
      AND si.project IN ('Seattle','VasTraffik 1','Vas Traffik 2/3','Stagecoach Bus Hops','Stagecoach South West','TRACS')
95 
      -- filter on defect type
96 
      AND si.issue_type IN ('Defect','FQT defect','Integration defect','Test Failure','Documentation')
97 
      -- filter on submitter
2207 brianf 98 
      AND us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
2195 brianf 99 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
100 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
101 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
2207 brianf 102 
                          'wwong','mchua','dmonahan','awehalle')
2195 brianf 103 
 
2207 brianf 104 
       --AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
2195 brianf 105 
      --                    'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
106 
      --                    'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
107 
      --                    'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')
108 
  ) df
109 
where
110 
    df.defect_datetime >= @start_date AND df.defect_datetime < @end_date
111 
GROUP BY df.defect_month,df.project