declare cur11_2 cursor for select count(*) from ahd.call_req left outer join ahd.ztr_his on ahd.call_req.persid = ahd.ztr_his.call_req_id where ahd.call_req.type='i' and (ahd.call_req.status in ('cl', 'ttpc')) and (ahd.ztr_his.to_status in ('l1wip', 'l2wip', 'icp', 'srbyl1', 'srbyl2', 'nccbyl1', 'nccbyl2', 'crbyl1', 'crbyl2')) and (ahd.call_req.open_date>@begin) and (ahd.call_req.open_date<@end) and (ahd.call_req.id = @cr_id)
open cur11_2
fetch cur11_2 into @count2
close cur11_2
deallocate cur11_2
if @count2 <> 0
set @handled = @handled + 1.0 / @count2 /*此人的处理完成数*/
fetch next from cur11_1 into @cr_id, @zh_id, @status, @to_status, @cnt, @open_date /*循环记录*/
end
close cur11_1
deallocate cur11_1
--total /*计算此人的处理故障数*/
declare cur11_3 cursor for select count(distinct(ahd.call_req.id)) from ahd.call_req left outer join ahd.ztr_his on ahd.call_req.persid = ahd.ztr_his.call_req_id where ahd.call_req.type='i' and (ahd.call_req.open_date>@begin and ahd.call_req.open_date<@end) and (ahd.ztr_his.to_cnt = @userid) /*取此人所有单*/
open cur11_3
fetch cur11_3 into @total /*总故障数*/
close cur11_3
deallocate cur11_3
--select @handled
--declare @handled float,@total float
--exec fgw_proc1_1 400115,1,1111111111,@handled output ,@total output
--print @handled
--print @total
go
b：每个员工的响应达标数、响应总数
fgw_proc2.txt
--fgw_proc2 响应达标数、响应总数
create procedure [ahd].[fgw_proc2](@开始时间 datetime , @结束时间 datetime)
as
declare @begin int , @end int
exec fgw_util1 @开始时间, @begin output
exec fgw_util1 @结束时间, @end output
declare @cr_id int, @zh_id int, @cnt int, @sym char(30), @time_stamp int, @isok int , @userid int , @handled int , @total int
declare @call_req_id char(30)
create table #temp_proc2 /* 响应达标数、响应总数*/
(
userid int,
handled2 int,
total2 int
)
create table #temp_proc2_1 /* 事件单为op的记录*/
(
cr_id int,
zh_id int,
cnt int,
isok int
)
--initialize #temp_proc2_1 /*已经op的单，是否响应达标，返回处理人*/
declare cur2_1 cursor for select zh.call_req_id,zh.id,zh.to_cnt,sd.sym,zh.time_stamp from ahd.ahd.call_req as cr left outer join ahd.ahd.ztr_his as zh on cr.persid=zh.call_req_id left outer join ahd.ahd.srv_desc as sd on cr.support_lev=sd.code where cr.type='i' and cr.open_date>@begin and cr.open_date<@end and (zh.to_status='astol1' or zh.to_status='astol2')
open cur2_1
fetch cur2_1 into @call_req_id, @zh_id, @cnt, @sym, @time_stamp /*事件单id,历史单id,人员,服务级别,op状态的时间*/
while @@fetch_status = 0
begin
exec fgw_proc2_1 @call_req_id , @sym , @time_stamp , @isok output
insert into #temp_proc2_1 values (@cr_id , @zh_id , @cnt , @isok)
fetch next from cur2_1 into @call_req_id, @zh_id, @cnt, @sym, @time_stamp
end
close cur2_1
deallocate cur2_1
--initialize #temp_proc2
declare cur2_2 cursor for select id from ahd.ahd.ctct
open cur2_2
fetch cur2_2 into @userid
while @@fetch_status = 0
begin
--get @total /*所有的已响应的单*/
declare cur2_3 cursor for select count(*) from #temp_proc2_1 where cnt = @userid
open cur2_3
