 |
|
|
 首页 |
名片设计 CorelDRAW Illustrator AuotoCAD Painter 其他软件 Photoshop Fireworks Flash |
|
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_proc1 exec fgw_proc1 1,1 2,fgw_proc2 CREATE PROCEDURE fgw_proc2(@userid int , @begin 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 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_proc2 exec fgw_proc2 1,1,1 3,fgw_proc3 CREATE 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 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_proc3 drop procedure fgw_proc3 EXEC fgw_proc3 1, 1111111111 4,fgw_proc4 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_zh INTO @cnt, @time_stamp1 IF @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 drop procedure fgw_proc4 exec fgw_proc4 1,\\\'1\\\',1,1,1 返回类别: 教程 上一教程: 一个复合查询方式 下一教程: 根据进程号获取该进程所在客户端的IP地址 您可以阅读与"存储过程-实践"相关的教程: · 处理孤立用户的存储过程 · 将表数据生成SQL脚本的存储过程 · SQL SERVER编写存储过程小工具(二) · 使用SQL SERVER的扩展存储过程实现远程备份与恢复 · 取汉字拼音首字母的存储过程 |
  |
| 快精灵印艺坊 版权所有 |
首页 会员中心在线印刷在线编辑付款方式索取样品设计指南连锁门店
|
||
