sql急。。。。。
请各位高手再帮我改下这个错误帮忙写出改法:谢谢!
错误提示:
Msg 8152, Level 16, State 13, Procedure sp_DeptYearPayAnlysise, Line 40
将截断字符串或二进制数据。
语句已终止。
代码如下:
--年度部门工资分析表
alter proc sp_DeptYearPayAnlysise @YYMM_FR CHAR(6),
@YYMM_TO CHAR(6)
AS
/*
create by :
create dt:
remark :exec sp_DeptYearPayAnlysise '201201','201209'
*/
SET NOCOUNT ON
CREATE table #Temp(YYMM CHAR(6),
PartID NVARCHAR(10),
dtName nvarchar(25),
per_count float DEFAULT(0),--总人数
pay_count float DEFAULT(0),--总工资
day_count float DEFAULT(0),--总天数
day_mtper float DEFAULT(0),--月平均出勤天数
pay_mtper float DEFAULT(0),--月平均工资
pay_dayper float DEFAULT(0),--日平均工资
persent float DEFAULT(0),--当月工资占全厂比率
total_month float default(0),
id int
)
select a.yymm,a.empno,b.PartID,t.dtName,p.WorkTime,a.incomeTaxBefor,a.DelCount,a.PayCount
into #MonthData
from PayMonthData a,peremployee b,PerDepart t,AttMonthData p
where a.empid =b.empid and b.partid = t.dtID and a.empid = p.empid and a.yymm=p.yymm
and a.yymm between @YYMM_FR AND @YYMM_TO
INSERT INTO #Temp(YYMM,PartID,dtName,per_count,pay_count,day_count,total_month)
SELECT YYMM,PartID,dtName,SUM(1),SUM(incomeTaxBefor),SUM(WorkTime)/8.0,(select sum(incomeTaxBefor) from PayMonthData where yymm = #MonthData.yymm)
FROM #MonthData
GROUP BY YYMM,PartID,dtName
update #Temp set day_mtper = round(day_count*1.00/(case when per_count>0 then per_count else 1 end),2),
pay_mtper = round(pay_count*1.00/(case when per_count>0 then per_count else 1 end),2),
pay_dayper= round(pay_count*1.00/(case when day_count>0 then day_count else 1 end),2)
update #Temp set persent = round(pay_count*1.00/(case when total_month>0 then total_month else 1 end),2)
where total_month >0
SELECT * FROM #Temp