fetch cur2_3 into @total
close cur2_3
deallocate cur2_3
--get @handled /*所有的已响应的单,并达标的单*/
declare cur2_4 cursor for select count(*) from #temp_proc2_1 where cnt = @userid and isok=1
open cur2_4
fetch cur2_4 into @handled
close cur2_4
deallocate cur2_4
insert into #temp_proc2 values (@userid , @handled , @total)
fetch next from cur2_2 into @userid
end
close cur2_2
deallocate cur2_2
drop table #temp_proc2_1
select * from #temp_proc2
drop table #temp_proc2
go
fgw_proc2_1.txt
--fgw_proc2_1
create procedure [ahd].[fgw_proc2_1](@call_req_id char(30) , @level char(30) , @time_stamp int , @isok int output)
as
set nocount on
set @isok = 0
declare cur_zh cursor for select time_stamp from ahd.ahd.ztr_his where call_req_id = @call_req_id and to_status in ('l1wip','l2wip') and time_stamp>@time_stamp
open cur_zh
declare @time_stamp1 int
set @time_stamp1=0
fetch cur_zh into @time_stamp1
if (@time_stamp1 is not null) and (@time_stamp1<>0)
begin
if charindex('一级', @level) is not null and charindex('一级', @level)<>0
begin
if @time_stamp1 - @time_stamp <600
set @isok=1
end
else if charindex('二级', @level) is not null and charindex('二级', @level)<>0
begin
if @time_stamp1 - @time_stamp <1800
set @isok=1
end
else if charindex('三级', @level) is not null and charindex('三级', @level)<>0
begin
if @time_stamp1 - @time_stamp <1800
set @isok=1
end
else if charindex('四级', @level) is not null and charindex('四级', @level)<>0
begin
if @time_stamp1 - @time_stamp <1800
set @isok=1
end
end
close cur_zh
deallocate cur_zh
--select @isok, @time_stamp1
go
c：每个员工的处理时限达标数，总数
fgw_proc3.txt
--fgw_proc3
create procedure fgw_proc3(@开始时间 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
create table #temp_proc3
(
userid int,
handled2 int,
total2 int
)
declare cur3_2 cursor for select id from ahd.ahd.ctct
open cur3_2
fetch cur3_2 into @userid
while @@fetch_status = 0
begin
--get @handled
declare cur3_4 cursor for select distinct(cr.id) from ahd.ahd.call_req as cr left outer join ahd.ahd.ztr_his as zh on cr.persid=zh.call_req_id where cr.type='i' and cr.open_date>@begin and cr.open_date<@end and zh.to_cnt = @userid and cr.sla_violation=0
open cur3_4
set @handled = @@cursor_rows
close cur3_4
deallocate cur3_4
--get @total
