| 2209 |
brianf |
1 |
:r ".\queries\SOM_Dashboard_query_init.sql"
|
|
|
2 |
|
|
|
3 |
IF OBJECT_ID('tempdb..#CQ_TEMP') IS NOT NULL DROP TABLE #CQ_TEMP;
|
|
|
4 |
|
|
|
5 |
SELECT * INTO #CQ_TEMP
|
|
|
6 |
FROM
|
|
|
7 |
(SELECT
|
|
|
8 |
df.defect_month,
|
|
|
9 |
df.project,
|
|
|
10 |
SUM(df.defect_critical) AS defect_severity_critical_count,
|
|
|
11 |
SUM(df.defect_important) AS defect_severity_important_count,
|
|
|
12 |
SUM(df.defect_routine) AS defect_severity_routine_count,
|
|
|
13 |
SUM(df.fixed) AS defect_fixed_count,
|
|
|
14 |
SUM(df.raised) AS defect_raised_count,
|
|
|
15 |
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,
|
|
|
16 |
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,
|
|
|
17 |
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,
|
|
|
18 |
CASE WHEN SUM(df.raised) = 0 THEN 0 ELSE SUM(df.fixed) * 1.0 /SUM(df.raised) END AS defect_ratio
|
|
|
19 |
FROM
|
|
|
20 |
(
|
|
|
21 |
-- get submitted defects
|
|
|
22 |
SELECT
|
|
|
23 |
si.issue_id,
|
|
|
24 |
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
|
|
|
25 |
CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
|
|
|
26 |
WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
|
|
|
27 |
ELSE si.project
|
|
|
28 |
END AS project,
|
|
|
29 |
si.project as sub_project,
|
|
|
30 |
CASE WHEN ((si.severity IS NULL) AND (si.priority = '1-Critical')) OR (si.severity = '1-Critical') THEN 1 ELSE 0 END AS defect_critical,
|
|
|
31 |
CASE WHEN ((si.severity IS NULL) AND (si.priority = '2-Important')) OR (si.severity = '2-Important') THEN 1 ELSE 0 END AS defect_important,
|
|
|
32 |
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,
|
|
|
33 |
|
|
|
34 |
1 AS raised,
|
|
|
35 |
DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date) AS defect_datetime,
|
|
|
36 |
sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
|
|
|
37 |
FROM
|
|
|
38 |
CQ_DEVI.admin.software_issue si,
|
|
|
39 |
CQ_DEVI.admin.statedef sd,
|
|
|
40 |
CQ_DEVI.admin.users us
|
|
|
41 |
WHERE
|
|
|
42 |
si.state = sd.id
|
|
|
43 |
AND si.submitter = us.dbid
|
|
|
44 |
--AND issue_id IN (57889,57987,61073,65474)
|
|
|
45 |
--AND si.issue_id IN (68144)
|
|
|
46 |
--AND si.project = 'Seattle'
|
|
|
47 |
-- filter on date
|
|
|
48 |
--AND si.submit_date >= '20110801' AND si.submit_date < '20111101'
|
|
|
49 |
-- filter on project
|
|
|
50 |
AND si.project IN ('Seattle','VasTraffik 1','Vas Traffik 2/3','Stagecoach Bus Hops','Stagecoach South West','TRACS')
|
|
|
51 |
-- filter on defect type
|
|
|
52 |
AND si.issue_type IN ('Defect','FQT defect','Integration defect','Test Failure','Documentation')
|
|
|
53 |
-- filter on submitter
|
|
|
54 |
AND (
|
|
|
55 |
(us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','lguntur',
|
|
|
56 |
'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
|
|
|
57 |
'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
|
|
|
58 |
'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
|
|
|
59 |
'wwong','mchua','dmonahan','awehalle'))
|
|
|
60 |
OR
|
|
|
61 |
(us.login_name IN ('fpatel') AND (DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.submit_date) < '20120130'))
|
|
|
62 |
)
|
|
|
63 |
|
|
|
64 |
--AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
|
|
|
65 |
-- 'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
|
|
|
66 |
-- 'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
|
|
|
67 |
-- 'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')
|
|
|
68 |
UNION
|
|
|
69 |
-- get closed defects
|
|
|
70 |
SELECT
|
|
|
71 |
si.issue_id,
|
|
|
72 |
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
|
|
|
73 |
CASE WHEN si.project IN ('Stagecoach Bus Hops','Stagecoach South West','TRACS') THEN 'UK'
|
|
|
74 |
WHEN si.project IN ('VasTraffik 1','Vas Traffik 2/3') THEN 'VasTraffik'
|
|
|
75 |
ELSE si.project
|
|
|
76 |
END AS project,
|
|
|
77 |
si.project as sub_project,
|
|
|
78 |
|
|
|
79 |
|
|
|
80 |
|
|
|
81 |
1 AS fixed,
|
|
|
82 |
|
|
|
83 |
DATEADD(Hour,-DATEDIFF(Hour, GETDATE(), GETUTCDATE()), si.close_date) AS defect_datetime,
|
|
|
84 |
sd.name as issue_state,si.submit_date,si.close_date,us.login_name,si.severity,si.priority,si.issue_type
|
|
|
85 |
FROM
|
|
|
86 |
CQ_DEVI.admin.software_issue si,
|
|
|
87 |
CQ_DEVI.admin.statedef sd,
|
|
|
88 |
CQ_DEVI.admin.users us
|
|
|
89 |
WHERE
|
|
|
90 |
si.state = sd.id
|
|
|
91 |
AND si.submitter = us.dbid
|
|
|
92 |
AND sd.name = 'Closed'
|
|
|
93 |
--AND si.issue_id IN (68144)
|
|
|
94 |
--AND issue_id IN (57889,57987,61073,65474)
|
|
|
95 |
--AND si.project = 'Seattle'
|
|
|
96 |
-- filter on date
|
|
|
97 |
--AND si.close_date >= '20110801' AND si.close_date < '20111101'
|
|
|
98 |
-- filter on project
|
|
|
99 |
AND si.project IN ('Seattle','VasTraffik 1','Vas Traffik 2/3','Stagecoach Bus Hops','Stagecoach South West','TRACS')
|
|
|
100 |
-- filter on defect type
|
|
|
101 |
AND si.issue_type IN ('Defect','FQT defect','Integration defect','Test Failure','Documentation')
|
|
|
102 |
-- filter on submitter
|
|
|
103 |
AND us.login_name IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
|
|
|
104 |
'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
|
|
|
105 |
'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
|
|
|
106 |
'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
|
|
|
107 |
'wwong','mchua','dmonahan','awehalle')
|
|
|
108 |
|
|
|
109 |
--AND us.fullname IN ('Andrew Phoon','Andrey Matveev','Angelo Serraino','Cherry Lee','Edis Kilic','Falguni Patel','Lakshmi Guntur',
|
|
|
110 |
-- 'Graeme Grieve','Hari Batna','Igor Dzukleski','Jason Godfrey','Jonathan Koh','Karthikeyan Murugesan','Ken Nguyen',
|
|
|
111 |
-- 'Ling Miao','Malarvizhi Thangavel','Nicolette Kerr','Ory Zaidenvorm','Rakesh Thumma','Roger O''Hearn','Sinmei Ip','Kelly Maesen',
|
|
|
112 |
-- 'Sonia Libao','Stephen Tan','Tarun Kumar','William Wah Kim Loo','Yohan Wehalle','Sree Radhakrishnan','Brian Boey','Sasithorn Ilyas','Wing Wong')
|
|
|
113 |
|
|
|
114 |
UNION
|
|
|
115 |
-- JIRA
|
|
|
116 |
-- get submitted defects
|
|
|
117 |
SELECT
|
|
|
118 |
si.id as issue_id,
|
|
|
119 |
DATEADD(mm,(YEAR(created)-1900)* 12 + MONTH(created) - 1, 0)AS defect_month, -- truncate to year,month
|
|
|
120 |
CASE WHEN pr.pname IN ('SLS Iteration Plan') THEN 'Stockholm'
|
|
|
121 |
ELSE pr.pname
|
|
|
122 |
END AS project,
|
|
|
123 |
pr.pname as sub_project,
|
|
|
124 |
CASE WHEN (sp.pname IN ('Critical','Blocker')) THEN 1 ELSE 0 END AS defect_critical,
|
|
|
125 |
CASE WHEN (sp.pname = 'Major') THEN 1 ELSE 0 END AS defect_important,
|
|
|
126 |
CASE WHEN (sp.pname IN ('Minor','Trivial')) THEN 1 ELSE 0 END AS defect_routine,
|
|
|
127 |
|
|
|
128 |
1 AS raised,
|
|
|
129 |
si.created AS defect_datetime,
|
|
|
130 |
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
|
|
|
131 |
FROM
|
|
|
132 |
JIRA.jirauser.jiraissue si,
|
|
|
133 |
JIRA.jirauser.issuestatus sd,
|
|
|
134 |
JIRA.jirauser.project pr,
|
|
|
135 |
JIRA.jirauser.issuetype st,
|
|
|
136 |
JIRA.jirauser.priority sp
|
|
|
137 |
WHERE
|
|
|
138 |
si.issuestatus = sd.id
|
|
|
139 |
AND si.project = pr.id
|
|
|
140 |
AND si.issuetype = st.id
|
|
|
141 |
AND si.priority = sp.id
|
|
|
142 |
-- filter on date
|
|
|
143 |
AND si.created >= '20090101'
|
|
|
144 |
-- filter on project
|
|
|
145 |
AND pr.pname IN ('SLS Iteration Plan','Cape Town')
|
|
|
146 |
-- filter on defect type
|
|
|
147 |
AND st.pname IN('Bug','Documentation')
|
|
|
148 |
-- filter on submitter
|
|
|
149 |
AND si.reporter IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
|
|
|
150 |
'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
|
|
|
151 |
'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
|
|
|
152 |
'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
|
|
|
153 |
'wwong','mchua','dmonahan','awehalle')
|
|
|
154 |
UNION
|
|
|
155 |
-- get closed defects
|
|
|
156 |
SELECT
|
|
|
157 |
si.id as issue_id,
|
|
|
158 |
DATEADD(mm,(YEAR(si.updated)-1900)* 12 + MONTH(si.updated) - 1, 0)AS defect_month, -- truncate to year,month
|
|
|
159 |
CASE WHEN pr.pname IN ('SLS Iteration Plan') THEN 'Stockholm'
|
|
|
160 |
ELSE pr.pname
|
|
|
161 |
END AS project,
|
|
|
162 |
pr.pname as sub_project,
|
|
|
163 |
|
|
|
164 |
|
|
|
165 |
|
|
|
166 |
1 AS fixed,
|
|
|
167 |
|
|
|
168 |
si.updated AS defect_datetime,
|
|
|
169 |
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
|
|
|
170 |
FROM
|
|
|
171 |
JIRA.jirauser.jiraissue si,
|
|
|
172 |
JIRA.jirauser.issuestatus sd,
|
|
|
173 |
JIRA.jirauser.project pr,
|
|
|
174 |
JIRA.jirauser.issuetype st,
|
|
|
175 |
JIRA.jirauser.priority sp
|
|
|
176 |
WHERE
|
|
|
177 |
si.issuestatus = sd.id
|
|
|
178 |
AND si.project = pr.id
|
|
|
179 |
AND si.issuetype = st.id
|
|
|
180 |
AND si.priority = sp.id
|
|
|
181 |
AND sd.pname = 'Closed'
|
|
|
182 |
-- filter on date
|
|
|
183 |
AND si.updated >= '20090101'
|
|
|
184 |
-- filter on project
|
|
|
185 |
AND pr.pname IN ('SLS Iteration Plan','Cape Town')
|
|
|
186 |
-- filter on defect type
|
|
|
187 |
AND st.pname IN('Bug','Documentation')
|
|
|
188 |
-- filter on submitter
|
|
|
189 |
AND si.reporter IN ('aphoon','amatveev','aserrain','clee','ekilic','fpatel','lguntur',
|
|
|
190 |
'ggrieve','hbatna','idzukleski','jgodfrey','jkoh','kmuruges','knguyen',
|
|
|
191 |
'lmiao','mthangav','nkerr','ozaidenv','rthumma','rohearn','sip','kmaesen2',
|
|
|
192 |
'slibao','stan','tkumar','wloo','ywehalle','sradhakr','bboey','silyas',
|
|
|
193 |
'wwong','mchua','dmonahan','awehalle')
|
|
|
194 |
) df
|
|
|
195 |
where
|
|
|
196 |
df.defect_datetime >= @start_date AND df.defect_datetime < @end_date
|
|
|
197 |
GROUP BY df.defect_month,df.project
|
|
|
198 |
) rs;
|
|
|
199 |
|
|
|
200 |
-- now return result with grans totals row
|
|
|
201 |
SELECT rs.*
|
|
|
202 |
FROM #CQ_TEMP rs
|
|
|
203 |
UNION
|
|
|
204 |
-- get grand totals row and include as PSDC project
|
|
|
205 |
SELECT
|
|
|
206 |
rs.defect_month,
|
|
|
207 |
'PSDC' as project,
|
|
|
208 |
SUM(rs.defect_severity_critical_count) AS defect_severity_critical_count,
|
|
|
209 |
SUM(rs.defect_severity_important_count) AS defect_severity_important_count,
|
|
|
210 |
SUM(rs.defect_severity_routine_count) AS defect_severity_routine_count,
|
|
|
211 |
SUM(rs.defect_fixed_count) AS defect_fixed_count,
|
|
|
212 |
SUM(rs.defect_raised_count) AS defect_raised_count,
|
|
|
213 |
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,
|
|
|
214 |
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,
|
|
|
215 |
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,
|
|
|
216 |
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
|
|
|
217 |
FROM #CQ_TEMP rs
|
|
|
218 |
GROUP BY rs.defect_month;
|
|
|
219 |
--ORDER BY rs.defect_month,rs.project;
|
|
|
220 |
|
|
|
221 |
DROP TABLE #CQ_TEMP;
|