|
Crystal Reports 和sql-server共同进行报表的开发--存储过程-实践(2) exec fgw_util1 @开始时间, @begin output
exec fgw_util1 @结束时间, @end output
DECLARE @userid int, @handled float, @total float
CREATE TABLE #temp_proc1
(
userid int,
handled float,
total float
)
DECLARE cur_ctct CURSOR FOR SELECT id FROM AHD.AHD.ctct --取所有的用户id
OPEN cur_ctct
FETCH cur_ctct INTO @userid
WHILE @@FETCH_STATUS = 0
BEGIN
--get @handle through exec fgw_proc2
EXEC fgw_proc1_1 @userid , @begin , @end , @handled output , @total output /*call下个存储过程，得到某个用户的解决数、接触故障数*/
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 /*释放*/
GO
fgw_proc1_1.txt
--fgw_proc1_1
CREATE PROCEDURE [AHD].[fgw_proc1_1](@userid int , @begin int , @end int , @handled float OUTPUT , @total float OUTPUT)
AS SET @handled = 0
SET @total = 0
DECLARE @cr_id int, @zh_id int, @status char(12), @to_status char(12), @cnt int, @open_date int
--handled /*计算此人的处理完成故障数*/
DECLARE cur11_1 CURSOR FOR SELECT AHD.call_req.id AS cr_id, AHD.ztr_his.id AS zh_id, AHD.call_req.status, AHD.ztr_his.to_status, AHD.ztr_his.to_cnt AS cnt, AHD.call_req.open_date 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.ztr_his.to_cnt = @userid
|
