ALM报表应用案例

james.zhong

//每月缺陷发现数统计
select month([BG_DETECTION_DATE])'月份',
sum(case when BG_USER_04 not like '用户'and  BUG.BG_USER_TEMPLATE_04='PHP'
and datediff(month,BG_DETECTION_DATE,BG_DETECTION_DATE)=0 then 1 else 0 end) 'PHP非用户发现缺陷数',
sum(case when BG_USER_04='用户' and  BUG.BG_USER_TEMPLATE_04='PHP'
and datediff(month,BG_DETECTION_DATE,BG_DETECTION_DATE)=0 then 1 else 0 end)'PHP用户发现缺陷数',

sum(case when BG_USER_04 not like '用户'and  BUG.BG_USER_TEMPLATE_04='FLASH'
and datediff(month,BG_DETECTION_DATE,BG_DETECTION_DATE)=0 then 1 else 0 end) 'FLASH非用户发现缺陷数',
  sum(case when BG_USER_04='用户' and  BUG.BG_USER_TEMPLATE_04='FLASH'
and datediff(month,BG_DETECTION_DATE,BG_DETECTION_DATE)=0 then 1 else 0 end)'FLASH用户发现缺陷数',

  sum(case when BG_USER_04 not like '用户'and  BUG.BG_USER_TEMPLATE_04='C++'
and datediff(month,BG_DETECTION_DATE,BG_DETECTION_DATE)=0 then 1 else 0 end) 'C++非用户发现缺陷数',
sum(case when BG_USER_04='用户' and  BUG.BG_USER_TEMPLATE_04='C++'
and datediff(month,BG_DETECTION_DATE,BG_DETECTION_DATE)=0 then 1 else 0 end)'C++用户发现缺陷数',count(*) '缺陷总数'


from bug where year([BG_DETECTION_DATE])=year(getdate())
group by month([BG_DETECTION_DATE]) order by  month([BG_DETECTION_DATE])
//用例检错率
SELECT
RCYC_NAME '周期名称',
sum(case when tc_status ='Failed' then 1 else 0 end)'失败数',
sum(case when tc_status ='Passed' then 1 else 0 end)'通过数',
sum(case when tc_status ='No Run' then 1 else 0 end)'No Run',
sum(case when tc_status ='Failed'or tc_status ='Passed' then 1 else 0 end)'执行用例数',
count(tc_status) '用例总数' ,
//CONVERT(VARCHAR(20),RCYC_START_DATE,102) '开始时间',
//CONVERT(VARCHAR(20),RCYC_END_DATE,102)  '结束时间',
MIN(CONVERT(VARCHAR(20),TESTCYCL.TC_VTS,102)) '执行日期',
MAX(CONVERT(VARCHAR(20),TESTCYCL.TC_VTS,102)) '完成日期',
//TESTCYCL.TC_VTS '执行日期',
CASE WHEN sum(case when tc_status ='Failed'or tc_status ='Passed' then 1 else 0 end)<>0 then 100*sum(case when tc_status ='Failed' then 1 else 0 end)/sum(case when tc_status ='Failed'or tc_status ='Passed' then 1 else 0 end) else 0 end '用例检错率',
1.0*datediff( mi,min(TESTCYCL.TC_VTS),max(TESTCYCL.TC_VTS))/60'总消耗工时(小时)',
CASE WHEN sum(case when tc_status ='Failed'or tc_status ='Passed' then 1 else 0 end)<>0 then datediff( mi,min(TESTCYCL.TC_VTS),max(TESTCYCL.TC_VTS))/sum(case when tc_status ='Failed'or tc_status ='Passed' then 1 else 0 end) else 0 end '每条用例耗时（分钟）'
FROM CYCLE c inner join cycl_fold on cy_folder_id = cf_item_id inner join testcycl on tc_cycle_id = cy_cycle_id inner join test on tc_test_id = ts_test_id inner join release_cycles on cy_assign_rcyc = rcyc_id
group by   RCYC_NAME, RCYC_START_DATE , RCYC_END_DATE
order by RCYC_NAME


//9月缺陷修复情况
SELECT BG_BUG_ID'缺陷ID',BG_SUMMARY'摘要',BG_DESCRIPTION'缺陷描述',BG_DETECTED_BY'检测者',AU_TIME'修复日期',BG_DETECTED_IN_RCYC'检测周期'
FROM AUDIT_PROPERTIES
INNER JOIN AUDIT_LOG ON AP_ACTION_ID=AU_ACTION_ID
INNER JOIN BUG ON AU_ENTITY_ID=BG_BUG_ID
WHERE AU_ENTITY_TYPE = 'BUG' AND AP_OLD_VALUE = 'Fixed' AND AP_NEW_VALUE = 'Closed' AND datediff(month,AU_TIME,dateadd(month,-2,getdate()))=0
ORDER BY BG_BUG_ID

james.zhong

//缺陷探测率
select b.sum0%a.sumc'PHP用户发现缺陷数',c.sum1%a.sumc'PHP非用户发现缺陷数',1.0*c.sum1/a.sumc'缺陷探测率',
b1.sum0%a.sumc'FLASH用户发现缺陷数',c1.sum1%a.sumc'FLASH非用户发现缺陷数',1.0*c1.sum1/a.sumc'缺陷探测率',
b2.sum0%a.sumc'C++用户发现缺陷数',c2.sum1%a.sumc'C++非用户发现缺陷数',1.0*c2.sum1/a.sumc'缺陷探测率'
from (select count(*) as sumc from bug) as a,
(select count(*) as sum0 from bug where BUG.BG_USER_04 ='用户'
and BG_USER_TEMPLATE_04='PHP') as b,(select count(*) as sum0 from bug where BUG.BG_USER_04 ='用户'and BG_USER_TEMPLATE_04='FLASH')as b1,(select count(*) as sum0 from bug where BUG.BG_USER_04 ='用户'and BG_USER_TEMPLATE_04='C++')as b2,
(select count(*) as sum1 from bug where BUG.BG_USER_04 not like '用户'
and BG_USER_TEMPLATE_04='PHP') as c,(select count(*) as sum1 from bug where BUG.BG_USER_04 not like '用户'and BG_USER_TEMPLATE_04='FLASH')as c1,(select count(*) as sum1 from bug where BUG.BG_USER_04 not like '用户'and BG_USER_TEMPLATE_04='C++')as c2