1, fgw_proc1: (according to the input time period, calculate the number of processing faults of each employee, stored in the temporary table # temp_proc1, finally returns these records, and release the resource. The resource will be released. Create Procedure FGW_PROC1 (@begin int, @end int) AS
Set NoCount on Declare @Userid Int, @handl 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_proc2 (called by FGW_PROC1, the number of processing faults used to perform specific employees, and returned to the caller in the way Output parameter @handled). Note that view: fgw_cr_n) crete procedure fgw_proc2 (@ Userid int, @end int, @handled 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 cur_crzh2 INTO @ count2 CLOSE cur_crzh2 DEALLOCATE cur_crzh2 IF @ count2 FETCH! = 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 @handleddrop procedure fgw_proc2exec fgw_proc2 1,1,13, fgw_proc3 (depending on the time period entered, calculated for each event Whether or not a single restriction requirement, stored in the temporary table # Temp_Proc3, finally returns these records, and release the resource. The resource is released. The FGW_PROC4 will be invoked to perform the match of the specified number of event orders) CREATE Procedure FGW_Proc3 (@begin int, @end Int )
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 outr 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 INTO @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_proc3drop procedure fgw_proc3EXEC fgw_proc3 1, 11111111114, fgw_proc4 (fgw_proc3 is invoked, for determining whether the specified number of single-event processing period Whether it is compliance request, @ CNT returns the user who handles the single, @ ISOK returns to the single handle processing, which will be determined according to different level event orders, different time limit requirements) Create 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_EN INTO @cnt, @ Time_Stamp1 IF @ Time_Stamp1! = 0 Begin if Charindex ('level', @LEVEL) is not null and charindex ('level', @LEVEL)! = 0 Begin IF @ Time_stamp1 - @time_stamp <600 set @ isok = 1 End else if Charindex ('secondary', @LEVEL) is not null and charindex ('secondary', @LEVEL)! = 0 begin if @ Time_stamp1 - @time_stamp < 1800 set @ isok = 1 END ELSE IF CHARINDEX ('Level 3', @LesL) IS Not Null and Charindex ('Level 3', @LEVEL)! = 0 Begin IF @ Time_Stamp1 - @time_stamp <1800 set @ iv = 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 endclose cur_zh deallocate cur_zh - -Select @ISOK, @ Time_Stamp1
Drop Procedure FGW_Proc4exec FGW_PROC4 1, '1', 1, 1, 1
