ALTER PROCEDURE [dbo].[UVYYKQ_YMJJ] @YM varchar(20), @KQKS varchar(20) AS begin Declare @OYM varchar(20) select @OYM=CONVERT(varchar(6), DATEADD(Month,-1,CAST((SUBSTRING(@YM,0,5)+'-'+SUBSTRING(@YM,5,7) +'-01') as datetime)), 112)--计算的人员条件 insert YYMonthHL(A0188,YM) select distinct(A0188),CONVERT(varchar(6), YYKQData.KQDate, 112) from YYKQData where CONVERT(varchar(6), YYKQData.KQDate, 112) = @YM and YYKQData.A0188 not in (select A0188 from YYMonthHL where YM=@YM) and YYKQData.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
update YYMonthHL set JJTS=0,BYBJ=0,BYGZ=0,BYYB=0,BYYJX=0,BYYX=0 where YM=@YM and A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--计算本月应休 update YYMonthHL set BYYX=round(cast(A.HZ as numeric(10,1))/2,1) from (select SUM( case when [am] in (78,87) then 1 else 0 end)+ SUM( case when [pm] in (78,87) then 1 else 0 end)+ SUM( case when [nt] in (78,87) then 1 else 0 end)+ 0 as HZ,@YM YM from YYMB where month=SUBSTRING(@YM,0,5)+'-'+SUBSTRING(@YM,5,7) and mode=1 group by month ) A where A.YM=YYMonthHL.YM and A.YM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) --计算已经休息 update YYMonthHL set BYYJX=A.XX from ( select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,round(cast(COUNT(KQ) as numeric(10,1))/2,1) as XX from ZSYY_KQ_HZ where KQ in(select ID from YyZD where XX=1 ) group by (A0188),CONVERT(varchar(6), KQDate, 112) ) A where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) --计算本月工作 update YYMonthHL set BYGZ=A.GZ from (select A0188,YYYYMM,SUM(GZ) as GZ from ( select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,round(cast(COUNT(KQ) as numeric(10,2))/2,2) as GZ from ZSYY_KQ_HZ where KQ in(select ID from YyZD where GZ=1 ) group by (A0188),CONVERT(varchar(6), KQDate, 112) ) kk group by A0188,YYYYMM)A where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) --计算夜班次数
update YYMonthHL set BYYB=A.JYB from (select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM, round(cast(COUNT(KQ) as numeric(10,2)),2) as JYB from ZSYY_KQ_HZ where KQ in(select ID from YyZD where JYB=1 ) and Type='NT' group by (A0188),CONVERT(varchar(6), KQDate, 112))A where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) --补假天数 ----PS:先计算上班天数大于模板中的上班天数,例如周六日勾选出勤累计积假。 update YYMonthHL set BYBJ=case when (isnull(BYGZ,0)-round(cast(A.HZ as numeric(10,2))/2,2))<0 then 0 else isnull(BYGZ,0)-round(cast(A.HZ as numeric(10,2))/2,2) end from (select SUM( case when [am] in (64) then 1 else 0 end)+ SUM( case when [pm] in (64) then 1 else 0 end)+ SUM( case when [nt] in (64) then 1 else 0 end)+ 0 as HZ,@YM YM from YYMB where month=SUBSTRING(@YM,0,5)+'-'+SUBSTRING(@YM,5,7) and mode=1 group by month ) A where A.YM=YYMonthHL.YM and A.YM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) ----PS:加上本月正补假天数update YYMonthHL set BYBJ=BYBJ+A.ZBXTS from ( select KK.A0188,kk.YYYYMM,SUM(kk.ZBXTS) as ZBXTS from ( select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM, sum(BXTS) as ZBXTS from ZSYY_KQ_HZ,YyZD where BXTS>0 and ZSYY_KQ_HZ.KQ=YyZD.ID group by (A0188),CONVERT(varchar(6), KQDate, 112) UNION ALL select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM, count(A0188)*1 as ZBXTS from ZSYY_KQ_HZ where Type='NT' and KQ='64' group by (A0188),CONVERT(varchar(6), KQDate, 112) ) KK group by A0188,YYYYMM) A where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) --本月积假天数 ----本月补假天数 update YYMonthHL set JJTS=BYBJ where YM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) ----PS累加上月积假天数 update YYMonthHL set JJTS=YYMonthHL.JJTS+ isnull(A.JJTS,0) from (select * from YYMonthHL where YM=@OYM) A where YYMonthHL.YM=@YM and A.A0188=YYMonthHL.A0188 and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) ----PS累加本月负补假天数 update YYMonthHL set JJTS=YYMonthHL.JJTS+ isnull(A.FBXTS,0) from ( select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,sum(BXTS) as FBXTS from ZSYY_KQ_HZ,YyZD where BXTS<0 and ZSYY_KQ_HZ.KQ=YyZD.ID group by (A0188),CONVERT(varchar(6), KQDate, 112)) A where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) ----PS本月白天中出现空白的一次扣减0.5 update YYMonthHL set JJTS=YYMonthHL.JJTS+ isnull(A.FBXTS,0) from ( select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM, count(A0188)*-0.5 as FBXTS from ZSYY_KQ_HZ where Type in ('AM','PM') and KQ in ('0','101') group by (A0188),CONVERT(varchar(6), KQDate, 112)) A where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS)) --select * from YYMonthHL end
DBCC FREEPROCCACHE
不保证有效,仅供建议。而且这个的效果等于重启机器,所以不要在正式客户环境下使用
你确认你的执行环境完全相同?
@YM varchar(20),
@KQKS varchar(20)
AS
begin
Declare @OYM varchar(20)
select @OYM=CONVERT(varchar(6),
DATEADD(Month,-1,CAST((SUBSTRING(@YM,0,5)+'-'+SUBSTRING(@YM,5,7) +'-01') as datetime)), 112)--计算的人员条件
insert YYMonthHL(A0188,YM)
select distinct(A0188),CONVERT(varchar(6), YYKQData.KQDate, 112)
from YYKQData where CONVERT(varchar(6), YYKQData.KQDate, 112) = @YM and
YYKQData.A0188 not in (select A0188 from YYMonthHL where YM=@YM)
and YYKQData.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
update YYMonthHL set JJTS=0,BYBJ=0,BYGZ=0,BYYB=0,BYYJX=0,BYYX=0
where YM=@YM and A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--计算本月应休
update YYMonthHL set BYYX=round(cast(A.HZ as numeric(10,1))/2,1)
from (select
SUM( case when [am] in (78,87) then 1 else 0 end)+
SUM( case when [pm] in (78,87) then 1 else 0 end)+
SUM( case when [nt] in (78,87) then 1 else 0 end)+
0 as HZ,@YM YM from YYMB
where month=SUBSTRING(@YM,0,5)+'-'+SUBSTRING(@YM,5,7) and mode=1 group by month ) A
where A.YM=YYMonthHL.YM and A.YM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--计算已经休息
update YYMonthHL set BYYJX=A.XX
from
(
select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,round(cast(COUNT(KQ) as numeric(10,1))/2,1) as XX
from ZSYY_KQ_HZ
where KQ in(select ID from YyZD where XX=1 )
group by (A0188),CONVERT(varchar(6), KQDate, 112)
) A where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--计算本月工作
update YYMonthHL set BYGZ=A.GZ
from
(select A0188,YYYYMM,SUM(GZ) as GZ from (
select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,round(cast(COUNT(KQ) as numeric(10,2))/2,2) as GZ
from ZSYY_KQ_HZ
where KQ in(select ID from YyZD where GZ=1 )
group by (A0188),CONVERT(varchar(6), KQDate, 112)
) kk group by A0188,YYYYMM)A where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--计算夜班次数
update YYMonthHL set BYYB=A.JYB
from
(select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM, round(cast(COUNT(KQ) as numeric(10,2)),2) as JYB from ZSYY_KQ_HZ
where KQ in(select ID from YyZD where JYB=1 ) and Type='NT'
group by (A0188),CONVERT(varchar(6), KQDate, 112))A
where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--补假天数
----PS:先计算上班天数大于模板中的上班天数,例如周六日勾选出勤累计积假。
update YYMonthHL set BYBJ=case when (isnull(BYGZ,0)-round(cast(A.HZ as numeric(10,2))/2,2))<0 then 0
else isnull(BYGZ,0)-round(cast(A.HZ as numeric(10,2))/2,2) end
from (select
SUM( case when [am] in (64) then 1 else 0 end)+
SUM( case when [pm] in (64) then 1 else 0 end)+
SUM( case when [nt] in (64) then 1 else 0 end)+
0 as HZ,@YM YM from YYMB
where month=SUBSTRING(@YM,0,5)+'-'+SUBSTRING(@YM,5,7) and mode=1 group by month ) A
where A.YM=YYMonthHL.YM and A.YM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
----PS:加上本月正补假天数update YYMonthHL set BYBJ=BYBJ+A.ZBXTS
from (
select KK.A0188,kk.YYYYMM,SUM(kk.ZBXTS) as ZBXTS from (
select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,
sum(BXTS) as ZBXTS from ZSYY_KQ_HZ,YyZD
where BXTS>0 and ZSYY_KQ_HZ.KQ=YyZD.ID
group by (A0188),CONVERT(varchar(6), KQDate, 112)
UNION ALL
select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,
count(A0188)*1 as ZBXTS from ZSYY_KQ_HZ
where Type='NT' and KQ='64' group by (A0188),CONVERT(varchar(6), KQDate, 112)
) KK group by A0188,YYYYMM) A
where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--本月积假天数
----本月补假天数
update YYMonthHL set JJTS=BYBJ where YM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
----PS累加上月积假天数
update YYMonthHL set JJTS=YYMonthHL.JJTS+ isnull(A.JJTS,0)
from (select * from YYMonthHL where YM=@OYM) A
where YYMonthHL.YM=@YM and A.A0188=YYMonthHL.A0188
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
----PS累加本月负补假天数
update YYMonthHL set JJTS=YYMonthHL.JJTS+ isnull(A.FBXTS,0)
from (
select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,sum(BXTS) as FBXTS from ZSYY_KQ_HZ,YyZD
where BXTS<0 and ZSYY_KQ_HZ.KQ=YyZD.ID
group by (A0188),CONVERT(varchar(6), KQDate, 112)) A
where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
----PS本月白天中出现空白的一次扣减0.5
update YYMonthHL set JJTS=YYMonthHL.JJTS+ isnull(A.FBXTS,0)
from (
select A0188,CONVERT(varchar(6), KQDate, 112) AS YYYYMM,
count(A0188)*-0.5 as FBXTS from ZSYY_KQ_HZ
where Type in ('AM','PM') and KQ in ('0','101') group by (A0188),CONVERT(varchar(6), KQDate, 112)) A
where A.YYYYMM=YYMonthHL.YM and A.A0188=YYMonthHL.A0188 and A.YYYYMM=@YM
and YYMonthHL.A0188 in (select A0188 from A01 where A01KQKS in (@KQKS))
--select * from YYMonthHL end