1, fgw_proc1: CREATE PROCEDURE fgw_proc1 (@begin int, @end int) AS SET NOCOUNT ON DECLARE @userid int, @handled float, @total float CREATE TABLE # temp_proc1 (userid int, handled float, total float) --get @ total DECLARE cur_cr CURSOR FOR SELECT count (*) FROM AHD.AHD.call_req where open_date> @begin and open_date <@end OPEN cur_cr FETCH cur_cr INTO @total CLOSE cur_cr DEALLOCATE cur_cr DECLARE cur_ctct CURSOR FOR SELECT id FROM AHD.AHD.ctct OPEN cur_ctct FETCH cur_ctct INTO @userid WHILE @@ FETCH_STATUS = 0 BEGIN --get @handle through exec fgw_proc2 EXEC fgw_proc2 @userid, @begin, @end, @handled output INSERT INTO # temp_proc1 VALUES (@userid, @handled, @total) FETCH NEXT FROM cur_ctct INTO @userid END CLOSE cur_ctct DEALLOCATE cur_ctct SELECT * FROM # temp_proc1 dROP TABLE # temp_proc1 drop procedure fgw_proc1exec fgw_proc1 1,12, fgw_proc2CREATE PROCEDURE fgw_proc2 (@userid int, @begin int, @end i NT, @HandLled Float Output) AS SET NOCOUNT ON Set @Handled = 0 Declare @Cr_id Int, @zh_id int, @status char (20), @TO_STATUS CHAR (20), @cnt int, @open_date int declare cur_crzh Cursor for SELECT * FROM AHD.dbo.FGW_CR_ZH where cnt = @userid OPEN cur_crzh FETCH cur_crzh INTO @cr_id, @zh_id, @status, @to_status, @cnt, @open_date WHILE @@ FETCH_STATUS = 0 BEGIN DECLARE @ count2 int DECLARE cur_crzh2 CURSOR FOR Select count (*) from ahd.dbo.fgw_cr_zh where cr_id = @cr_id and open_date> @begin and open_date <@ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @
end OPEN cur_crzh2 FETCH cur_crzh2 INTO @ count2 CLOSE cur_crzh2 DEALLOCATE cur_crzh2 IF @ count2! = 0 SET @handled = @handled 1 / @ count2 FETCH NEXT FROM cur_crzh INTO @cr_id, @zh_id, @status, @to_status, @cnt, @ open_date END CLOSE cur_crzh DEALLOCATE cur_crzh --SELECT @handled drop procedure fgw_proc2exec fgw_proc2 1,1,13, fgw_proc3CREATE PROCEDURE fgw_proc3 (@begin int, @end int) AS SET NOCOUNT ON DECLARE @cr_id int, @zh_id int, @cnt int, @SYM Char (30), @time_stamp int, @isok int create table # temp_proc3 (cr_id int, zh_id int, cnt int, isok int) Declare cur_crzhsd cursor for select cr.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 = 'op' open cur_crzhsd fetch cur_crzhsd @cr_id, @zh_id, @cnt, @sym, @time_stamp WHILE @@ FETCH_STATUS = 0 BEGIN --get @handle through exec fgw_proc2 EXEC fgw_proc4 @zh_id, @sym, @time_stamp, @cnt output, @isOK output INSERT INTO # temp_proc3 VALUES (@cr_id, @zh_id, @cnt, @isOK) FETCH NEXT FROM cur_crzhsd INTO @cr_id, @zh_id, @cnt, @sym, @time_stamp END CLOSE cur_crzhsd DEALLOCATE cur_crzhsd SELECT * FROM # temp_proc3 dROP TABLE # temp_proc3 drop procedure fgw_proc3EXEC fgw_proc3 1, 11111111114, fgw_proc4CREATE PROCEDURE fgw_proc4 (@
zh_id int, @level char (30), @time_stamp int, @cnt int OUTPUT, @isOK int OUTPUT) AS SET NOCOUNT ON SET @isOK = 0 DECLARE cur_zh CURSOR FOR SELECT to_cnt, time_stamp FROM AHD.AHD.ztr_his WHERE id = @zh_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 @cnt, @ time_stamp1 IF @ time_stamp1! = 0 BEGIN IF CHARINDEX ( 'a Level ', @LEVEL) IS Not Null and Charindex (' level ", @LEVEL)! = 0 Begin if @ Time_Stamp1 - @time_stamp <600 set @ isok = 1 END ELSE IF Charindex ('Second', @LEVEL) Is Not Null and Charindex ('Level 2', @LEVEL)! = 0 Begin if @ Time_stamp1 - @time_stamp <1800 set @ isok = 1 END ELSE IF Charindex ('Level 3', @LEVEL) IS NOT NULL AND CHARINDEX ( 'Level three', @LEVEL! = 0 Begin if @ Time_stamp1 - @time_stamp <1800 set @ isok = 1 END ELSE IF Charindex ('Level 4', @LEVEL) IS Not Null and Charindex ('4th ", @ Level)! = 0 Begin if @ Time_stamp1 - @time_stamp <1800 set @ isok = 1 End End CLOSE CUR_EN D Eallocate cur_zh --select @ISOK, @ Time_stamp1 Drop Procedure FGW_Proc4exec FGW_Proc4 1, '1', 1, 1, 1
