因报表经常计算百分比之类的,但平常的SQL查询比较慢,适合使用Procedure以优化程序。标签:报表,pa,workid,part2,Vegas,members,year,potential,Procedure From: https://blog.51cto.com/amadeus/5751660
---Vegas Created 2007/10/16
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[pms_potential_Chart]
@i int , --potential
@j nvarchar(50), --dept
@year nvarchar(50),
@pa nvarchar(50)
as
declare @a int
declare @b int
declare @name nvarchar(50)
declare @month int
if @year='-1' ----year
begin
select @year=substring(CONVERT(varchar, getdate(), 120 ),1,4)
end
if @pa='-1' -----pa
begin
select @month=month(getdate())
if @month<6
begin
set @pa='1'
end
else
begin
set @pa='2'
end
end
if @i=1 ----potential
begin
set @name='HPP'
end
else if @i=2
begin
set @name='VP'
end
else if @i=3
begin
set @name='BCP'
end
if @j='all' ---dept
begin
select @a=count(distinct part2.workid) from part2,members where members.workid=part2.workid and part2.potential=@i and part2.year=@year and part2.pa_type=@pa
select @b=count(distinct part2.workid) from part2,members where potential is not null and potential<>0 and members.workid=part2.workid and part2.year=@year and part2.pa_type=@pa
select @name,@a/@b*1.0
end
else
if @j='DTS' or @j='DTM'
begin
select @a=count(distinct part2.workid) from part2,members where members.workid=part2.workid and members.dept_id like @j and part2.potential=@i and part2.year=@year and part2.pa_type=@pa
select @b=count(distinct part2.workid) from part2,members where potential is not null and potential<>0 and members.workid=part2.workid and members.dept_id= @j and part2.year=@year and part2.pa_type=@pa
select @name,@a/@b*1.0
end
else
begin
select @a=count(distinct part2.workid) from part2,members where members.workid=part2.workid and members.dept_id like @j and part2.potential=@i and part2.year=@year and part2.pa_type=@pa
select @b=count(distinct part2.workid) from part2,members where potential is not null and potential<>0 and members.workid=part2.workid and members.dept_id like @j+'___' and part2.year=@year and part2.pa_type=@pa
select @name,@a/@b*1.0
end