请高手帮忙优化以下SQL存储过程，（400人的30天的考勤处理）

lyq 2007-11-22 04:48:13

CREATE PROCEDURE [dbo].[KqCalc] @sdate datetime,@edate datetime AS
--初始化
set nocount on
declare @tmpdate0 datetime,@nmonths int
IF @edate<@sdate
begin
set @tmpdate0=@sdate set @sdate=@edate set @edate=@tmpdate0
end
set @nmonths=year(@edate+1)*12+MONTH(@edate+1)-YEAR(@sdate-1)*12-MONTH(@sdate-1)+1
declare
@tmpdate varchar(20), @tmpdate1 datetime,@currqpre varchar(10),@currq varchar(10) ,@currqnext varchar(20),@csjfwa1 varchar(20),@csjfwa2 varchar(20),@csjfwb1 varchar(20),@csjfwb2 varchar(20),@sjtmp varchar(20),
------------------------------定义过程省略。。。。太长了。。CSDN粘不上。
--初始化
CREATE TABLE #kqtjtmp(bh varchar(10), kh varchar(20), xm varchar(10), rq varchar(12),sj1a varchar(10) not null default '', sj1b varchar(10) not null default '', sj2a varchar(10) not null default '', sj2b varchar(10) not null default '', sj3a varchar(10) not null default '' ,sj3b varchar(10) not null default '', cdsj int not null default 0, ztsj int not null default 0, jbsj int not null default 0, sbsj int not null default 0, bcsm varchar(50) not null default '', bm varchar(50) not null default '' )
IF EXISTS (SELECT id FROM tempdb..sysobjects WHERE name = '##count') DROP TABLE ##count create table ##count(id int)
IF EXISTS (SELECT id FROM tempdb..sysobjects WHERE name = '#datatmp') DROP TABLE #datatmp select distinct bh,kh,convert(varchar(8),rq,112) rq, REPLACE(CONVERT(char(5), sj, 8), ':', '') sj into #datatmp from rlkh.dbo.yskq where rq between @sdate and @edate order by bh,kh,rq
IF EXISTS (SELECT id FROM tempdb..sysobjects WHERE name = '#kqtjtmp') DROP TABLE #kqtjtmp
IF EXISTS (SELECT id FROM tempdb..sysobjects WHERE name = '#sjtmp') DROP TABLE #sjtmp create table #sjtmp (sj varchar(20))
IF EXISTS (SELECT id FROM tempdb..sysobjects WHERE name = '#kqsj_bb') DROP TABLE #kqsj_bb SELECT bc, Rq, sj1, sj2, sbfw, xbfw into #kqsj_bb FROM rlkh.dbo.kqsj_bb where rq between @sdate and @edate
IF EXISTS (SELECT id FROM tempdb..sysobjects WHERE name = '#qj') DROP TABLE #qj SELECT xmbh, rq, zl, lx into #qj FROM tl.dbo.qj where rq between @sdate and @edate
delete from kqhz where kjyear=@kjyear and kjmonth=@kjyear
delete from kqmx where year(rq)=@kjyear and month(rq)=@kjmonth
set @ndays= datediff(day,@sdate,@edate)

--根据人事库处理考勤--------------- 大约400人--------------------- 处理日期为每月1-月底。
declare Emp_cur cursor for Select bh,kh,bm,xm,bc From rlkh.dbo.employee where kh<>'' order by bh
open Emp_cur
fetch next from Emp_Cur into @bh,@kh,@bm,@xm,@bc
while (@@FETCH_STATUS = 0)
begin
set @j=0 set @csjfwa1_i=0 set @csjfwb1_i=0 set @csjfwa2_i=0 set @csjfwb2_i=0 set @sjtmp=''
while @j<=@ndays
begin
--开始j
set @nsbsj=0
set @currqpre=convert(varchar(8),@sdate+@j-1,112)
set @currq= convert(varchar(8), @sdate+@j,112)
set @currqnext=convert(varchar(8), @sdate+@j+1,112)
delete from #sjtmp
insert into #sjtmp select rq+sj FROM #datatmp WHERE bh=@bh AND rq between @currqpre and @currqnext ORDER BY 1
INSERT INTO #kqtjtmp (kh,xm,rq,bm,bh) VALUES(@kh,@xm,@currq,@bm,@bh)

--节假日
set @mc=''
select @mc=mc from rlkh.dbo.jjr where rq=@currq
if @mc<>''
update #kqtjtmp set bcsm='(节:'+@mc+')'
else
begin
--开始非节
--请假
select @mc=zl+lx from #qj where rq=@currq and xmbh=@bh
set @lqj=0
if @mc<>''
begin
update #kqtjtmp set bcsm='(请:'+@mc+')'
set @lqj=1
end
-排班
select @sj1=sj1, @sj2=sj2, @sbfw=sbfw, @xbfw=xbfw from #kqsj_bb where bc=@bc and rq=@currq
IF EXISTS (SELECT id FROM tempdb..sysobjects WHERE name = '##A') DROP TABLE ##A
create table ##A (id int ,a varchar(4),b varchar(4) )
insert into ##A (id,a,b) values (1,LEFT(@sj1,4),RIGHT(@sj1,4) )
insert into ##A (id,a,b) values (2,LEFT(@sj2,4),RIGHT(@sj2,4) )
insert into ##A (id,a,b) values (3,LEFT(@sj3,4),RIGHT(@sj3,4) )
insert into ##A (id,a,b) values (4,cast(LEFT(@sbfw,3) as integer),cast(RIGHT(@sbfw,3)as integer))
insert into ##A (id,a,b) values (5,cast(LEFT(@xbfw,3) as integer),cast(RIGHT(@xbfw,3) as integer))
IF LEN(@jbsj)<>4 set @cjbsj='' ELSE set @cjbsj=@jbsj
if (select count(sj) from #sjtmp)>0
begin
--开始nsjs
set @i=1
while @i<=2
begin --开始j
set @lsb=0 set @nsbsj1=0
if (select len(a)+len(b) from ##A where id=@i) >= 4
begin
--处理过程，太长了，CSDN没有办法贴。。。。。。。。。。。。。。。。。。。。。a
end
set @i=@i+1
end --结束i
end --结束nsjs
end --结束非结
--出勤\缺勤统计
set @okcs=0 set @qqcs=0 set @qqfk=0 set @ztfk=0 set @cdfk=0 set @sj=0 set @cclx=0 set @cqts=0
select @sj1a=sj1a,@sj1b=sj1b,@sj2a=sj2a,@sj2b=sj2b,@bcsm=bcsm from #kqtjtmp
if (@bcsm='(请:轮休整)' or @bcsm='(请:出差整)') begin set @cclx=1 set @cqts=1 end
else if (@bcsm='(请:轮休半)' or @bcsm='(请:出差半)') begin set @cclx=0.5 set @cqts=1 end
else if (@bcsm='(请:事假整)') begin set @sj=1 set @cqts=0 end
else if (@bcsm='(请:事假半)') begin set @sj=0.5 set @cqts=0.5 end
else
begin
if @sj1a in ('ok','迟到','早退') set @okcs=@okcs+1 else if @sj1a='--' set @qqcs=@qqcs+1
if @sj1b in ('ok','迟到','早退') set @okcs=@okcs+1 else if @sj1b='--' set @qqcs=@qqcs+1
if @sj2a in ('ok','迟到','早退') set @okcs=@okcs+1 else if @sj2a='--' set @qqcs=@qqcs+1
if @sj2b in ('ok','迟到','早退') set @okcs=@okcs+1 else if @sj2b='--' set @qqcs=@qqcs+1
if @okcs in (1,2,3) set @cqts=0.5 else if @okcs=4 set @cqts=1 else set @cqts=0
if @qqcs in (1,2,3) begin set @qqfk=10*@qqcs set @cqts=1 end else if @qqcs=4 set @cqts=0
end
--迟到早退罚款统计
if @cdsj>0 and @cdsj<=5 set @cdfk=1 else if (@cdsj >5 and @cdsj<=15) set @cdfk=2 else if (@cdsj >15 and @cdsj<=30) set @cdfk=5 else set @cdfk=0
if @ztsj > 0 and @ztsj<=30 set @ztfk=5 else set @ztfk=0
--添加至考勤汇总表
if exists(select bh from kqhz where bh=@bh and kjyear=@kjyear and kjmonth=@kjmonth )
update kqhz set cclxdays=cclxdays+@cclx,qjdays=qjdays+@sj,cqdays=cqdays+@cqts,cdfk=cdfk+@cdfk,ztfk=ztfk+@ztfk,qqfk=qqfk+@qqfk,zfk=zfk+@cdfk+@ztfk+@qqfk where bh=@bh and kjyear=@kjyear and kjmonth=@kjmonth
else
insert into kqhz (kjyear, kjmonth, xm, bh, bm, kh, cclxdays, qjdays, cqdays, jsdays, cdfk, ztfk, qqfk,zfk)
values(year(@sdate),month(@sdate),@xm,@bh,@bm,@kh,@cclx,@sj,@cqts,0,@cdfk,@ztfk,@qqfk,@cdfk+@ztfk+@qqfk)
insert into kqmx (bh, kh, xm, rq, sj1a, sj1b, sj2a, sj2b,sj3a,sj3b,cdsj, ztsj,jbsj,sbsj, bcsm, bm) select * from #kqtjtmp
insert into ##count (id)values(0)
delete from #kqtjtmp

set @j=@j+1
end --结束j
fetch next from Emp_Cur into @bh,@kh,@bm,@xm,@bc
end
close Emp_cur
deallocate Emp_cur
GO

...全文