ALTER proc [dbo].[Get_DispatchingList]
(
@Stime datetime,
@Etime datetime,
@Rid int
)
as
begin
declare @strsql varchar(8000)
set @strsql ='dbo.F_FindParentDeptNameByRepositoryId(r.id) as RepositoryName, isnull(d.Num,0) as OutNmu, isnull(c.Num,0)as inNum,
isnull(e.num,0) as Days,convert(varchar(50), case when a.num is null then 0 else case when b.num is null then 100 else round((isnull(a.num,0)*100)/(isnull(a.num,0)+isnull(b.num,0))*1,0)
end end )+ '%'as AccuracyRate
from RepositoryInfo r left join DispatchingList d1 on d1.outRepId=r.id
left join DispatchingList d2 on d2.inRepId=r.id
left join orderinfo d3 on d1.orderinfoId=d3.id
left outer join (select d4.inrepId,isnull(avg(datediff(day,d3.finishedtime,d4.confirmTime)+1),0) as num
from DispatchingList d4 left join orderinfo d3 on d4.orderinfoId=d3.id where d4.status=2 and d4.confirmTime>[email protected] AND d4.confirmTime<[email protected] group by d4.inrepId) e on r.id=e.inrepId
left outer join (select outrepId,sum(outnum) as num from DispatchingList where status=2 and confirmTime>[email protected] AND confirmTime<[email protected] group by outrepId) d on r.id=d.outrepId
left outer join (select inrepId,sum(outnum) as num from DispatchingList where status=2 and confirmTime>[email protected] AND confirmTime<[email protected] group by inrepId) c on r.id=c.inrepid
left outer join (select inrepId,count(1) as num from DispatchingList where status=2 and confirmTime>[email protected] AND confirmTime<[email protected] group by inrepId) a on r.id=a.inrepid
left outer join (select inrepId,count(1) as num from DispatchingList where status=4 and confirmTime>[email protected] AND confirmTime<[email protected] group by inrepId) b on r.id=b.inrepid
WHERE [email protected] and d1.confirmTime>[email protected] AND d1.confirmTime<[email protected] or d2.confirmTime>[email protected] AND d2.confirmTime<[email protected]
group by r.id,a.num,b.num,e.num,d.num,c.num order by r.id desc'
exec (@strsql)
end
------解决方案--------------------
改为nvarchar类型试试