sql问题
在存储过程中创建表是用来干什么的??求解。。
再帮忙看看这个地方有什么不对??
为什么会出现这样的错误,这是哪里错了?
错误提示:(Msg 8134, Level 16, State 1, Procedure sp_XNLMonthMoneyAnlysise_GL, Line 51
遇到以零作除数错误。语句已终止。)
代码如下:
CREATE proc sp_XNLMonthMoneyAnlysise_GL @yymm char(6)
as
set nocount on
/*
exec sp_XNLMonthMoneyAnlysise_GL '201202'
*/
create table #temp(YYMM char(6),
cType nvarchar(20),
P_count int,--人数
total_pay float,--总工资
Cq_Days float,--出勤天数
avg_day float,--平均出勤天数
avg_pay float,--平均日工资
per_total float,--占总人数比率
pay_total float--占总工资比率
)
select P.*,E.Comedate,GZ_Month=Datediff(month,E.comedate,
cast(convert(char(6),cast(@yymm+'01' as smalldatetime)+33,112)+'01' as smalldatetime)
)
into #PayMonthData
from PayMonthData P,Peremployee E WHERE P.EMPNO = E.EMPNO
select YYMM,cType=(case when GZ_Month>=0 and GZ_Month<=1 then '0~1个月'
when GZ_Month>1 and GZ_Month<=2 then '1~2个月'
when GZ_Month>2 and GZ_Month<=6 then '3~6个月'
when GZ_Month>6 and GZ_Month<=12 then '6~12个月'
when GZ_Month>12and GZ_Month<=18 then '1~1.5年'
when GZ_Month>18 and GZ_Month<=24 then '1.5~2年'
when GZ_Month>24 and GZ_Month<=36 then '2~3年'
when GZ_Month>36 and GZ_Month<=48 then '3年'
when GZ_Month>48 then '3年以上'
else '' END),
isnull(PayNo2,0)as PayNo2,PayCount
into #temp2
from #PayMonthData where yymm =@yymm
insert into #temp(yymm,cType,P_count,total_pay,Cq_Days,avg_day,per_total,pay_total)
select yymm,cType,sum(1)as P_count ,sum(PayCount) as total_pay, sum(PayNo2) as Cq_Days ,0 as avg_day,0 as per_total,0 as pay_total
from #temp2 group by yymm, cType
update #temp set avg_day =Cq_days*1.00/P_count
update #temp set avg_pay = (case when Cq_days = 0 then 0 else Total_pay/Cq_Days end)
update #temp set per_total = P_count*1.00/(select sum(P_count) from #temp)
update #temp set pay_total = total_pay*1.00/(select sum(total_pay) from #temp)
select * from #temp