Subversion Repositories DevTools

Rev

Rev 2195 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 2195 Rev 2197
Line 14... Line 14...
14 
FROM
 14 
FROM
15 
  (
 15 
  (
16 
  -- get submitted defects
 16 
  -- get submitted defects
17 
  SELECT
17 
  SELECT
18 
       si.issue_id,
 18 
       si.issue_id,
19 
       DATEADD(mm,(YEAR(submit_date)-1900)* 12 + MONTH(submit_date) - 1, 0)AS defect_month, -- truncate to year,month
 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
20 
       CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
 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'
 21 
            WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
22 
            ELSE si.project
 22 
            ELSE si.project
23 
       END AS project,
 23 
       END AS project,
24 
       si.project as sub_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,
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,
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,
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 
       0 AS fixed,
 28 
       0 AS fixed,
29 
       1 AS raised,
 29 
       1 AS raised,
30 
       si.submit_date AS defect_datetime,
30 
       DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date) AS defect_datetime,
31 
       sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
 31 
       sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
32 
   FROM
 32 
   FROM
33 
     CQ_DEVI.admin.software_issue si,
 33 
     CQ_DEVI.admin.software_issue si,
34 
     CQ_DEVI.admin.statedef sd,
 34 
     CQ_DEVI.admin.statedef sd,
35 
     CQ_DEVI.admin.users us
 35 
     CQ_DEVI.admin.users us
Line 48... Line 48...
48 
      -- filter on submitter
 48 
      -- filter on submitter
49 
      AND us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
 49 
      AND us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
50 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
 50 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
51 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
 51 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
52 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
 52 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
53 
                          'wwong','mchua','dmonahan')
 53 
                          'wwong','mchua','dmonahan','awehalle')
54
54
55 
      --AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
 55 
      --AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
56 
      --                    'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
 56 
      --                    'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
57 
      --                    'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
 57 
      --                    'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
58 
      --                    'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')
 58 
      --                    'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')
59 
  UNION
59 
  UNION
60 
  -- get closed defects
 60 
  -- get closed defects
61 
  SELECT
61 
  SELECT
62 
       si.issue_id,
 62 
       si.issue_id,
63 
       DATEADD(mm,(YEAR(close_date)-1900)* 12 + MONTH(close_date) - 1, 0)AS defect_month, -- truncate to year,month
 63 
       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
64 
       CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
 64 
       CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
65 
            WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
 65 
            WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
66 
            ELSE si.project
 66 
            ELSE si.project
67 
       END AS project,
 67 
       END AS project,
68 
       si.project as sub_project,
 68 
       si.project as sub_project,
69 
       0 AS defect_critical,
69 
       0 AS defect_critical,
70 
       0 AS defect_important,
70 
       0 AS defect_important,
71 
       0 AS defect_routine,
71 
       0 AS defect_routine,
72 
       1 AS fixed,
 72 
       1 AS fixed,
73 
       0 AS raised,
 73 
       0 AS raised,
74 
       si.close_date AS defect_datetime,
74 
       DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.close_date) AS defect_datetime,
75 
       sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
 75 
       sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
76 
   FROM
 76 
   FROM
77 
     CQ_DEVI.admin.software_issue si,
 77 
     CQ_DEVI.admin.software_issue si,
78 
     CQ_DEVI.admin.statedef sd,
 78 
     CQ_DEVI.admin.statedef sd,
79 
     CQ_DEVI.admin.users us
 79 
     CQ_DEVI.admin.users us
Line 93... Line 93...
93 
      -- filter on submitter
 93 
      -- filter on submitter
94 
      AND us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
 94 
      AND us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
95 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
 95 
                          'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
96 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
 96 
                          'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
97 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
 97 
                          'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
98 
                          'wwong','mchua','dmonahan')
 98 
                          'wwong','mchua','dmonahan','awehalle')
99 
 
 99 
 
100 
      --AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
 100 
      --AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
101 
      --                    'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
 101 
      --                    'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
102 
      --                    'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
 102 
      --                    'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
103 
      --                    'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')
 103 
      --                    'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')