自定义公式的计算处理

原帖地址:
http://community.csdn.net/Expert/topic/3485/3485588.xml?temp=.8813745

--示例数据
create table sale(date datetime,code varchar(10),amt int)
insert sale select @#2004-10-22@#,@#aa@#,15000
union  all  select @#2004-10-22@#,@#bb@#,18000
union  all  select @#2004-10-22@#,@#clearcase/" target="_blank" >cc@#,20000
union  all  select @#2004-10-23@#,@#aa@#,21000
union  all  select @#2004-10-23@#,@#bb@#,18500
union  all  select @#2004-10-23@#,@#cc@#,19600

create table dept(code varchar(10),name varchar(10))
insert dept select @#aa@#,@#中餐厅@#
union  all  select @#bb@#,@#西餐厅@#
union  all  select @#cc@#,@#客房部@#
union  all  select @#dd@#,@#KTV部@#

create table cost(date datetime,code varchar(10),amt int)
insert cost select @#2004-10-22@#,@#aa@#,5000
union  all  select @#2004-10-22@#,@#bb@#,7000
union  all  select @#2004-10-22@#,@#cc@#,11000
union  all  select @#2004-10-23@#,@#aa@#,12000
union  all  select @#2004-10-23@#,@#bb@#,8500
union  all  select @#2004-10-23@#,@#cc@#,9600

create table means(code varchar(10),seq int,[desc] varchar(10),formula nvarchar(4000))
insert means select @#aa@#,1,@#销售收入@#,@#^sale@amt^@#
union  all   select @#aa@#,2,@#销售成本@#,@#^cost@amt^@#
union  all   select @#aa@#,3,@#上交利润@#,@#([1] - [2])*0.3@#
union  all   select @#aa@#,4,@#净利润@#  ,@#[1]-[2]-[3]@#
go

/*--问题处理要求描述

 写一个计算的存储过程,完成根据各基础资料及计算方法,将数据放到利润明细表中!

 如用户输入的查询条件是部门代号aa(@code),日期为:2004-10-22(@date)

 则处理过程如下,
 1:从利润计算方法表(means)读取code = aa到临时表
   select * into #temp from means where code = @code order by seq

 2:用游标循环处理每一个项目,按seq从小到大的顺序,如第一个项目
   insert into gain_detail(code,date,desc,amt)
      select @code,@date,@desc,sale.amt from sale where code = @code and date = @date
      其它项目类似

 公式说明:
 1.^表名@字段名^ : 例如: ^sale@amt^ 表示从 sale 表取 amt 字段的值,取值条件是 code=@code and date=@date
 2.[seq]        : 例如: [1]-[2]-[3],[]之间为引用本部门前面的计算结果项,[1]表示是本部门的销售收入[2]表示销售成本,其它类似
 3.其他的是标准的计算表达式
--*/

--问题处理:
--公式计算的存储过程
create proc p_calc
@formula nvarchar(4000), --要计算的公式
@code varchar(10),       --部门代码
@date datetime,          --计算的日期
@amt int out             --计算的结果
as
declare @s1 nvarchar(4000),@s2 nvarchar(4000),@i int,@j int

--外部计算
set @i=patindex(@#%^%@%^%@#,@formula)
while @i>0
begin
 select @j=charindex(@#@@#,@formula,@i)
  ,@s1=substring(@formula,@i,@j-@i)
  ,@s2=@# from @#
   +substring(@formula,@i+1,@j-@i-1)
   +@# where code=@code and date=@date@#
  ,@i=charindex(@#^@#,@formula,@j)
  ,@s1=@s1+substring(@formula,@j,@i-@j+1)
  ,@s2=@#select @amt=@#
   +substring(@formula,@j+1,@i-@j-1)
   +@s2
  exec sp_executesql @s2
   ,N@#@code varchar(10),@date datetime,@amt int out@#
   ,@code,@date,@amt out
  select @formula=replace(@formula,@s1,@amt)
   ,@i=patindex(@#%^%@%^%@#,@formula)
end

--计算内部公式
select @i=patindex(@#%[[]%]%@#,@formula)
while @i>0
begin
 select @j=charindex(@#]@#,@formula,@i)
  ,@s1=substring(@formula,@i,@j-@i+1)
  ,@s2=@#select @amt=amt from #t where seq=@#
   +substring(@formula,@i+1,@j-@i-1)
  exec sp_executesql @s2,N@#@amt int out@#,@amt out
  select @formula=replace(@formula,@s1,@amt)
   ,@i=patindex(@#%[[]%]%@#,@formula)  
end

--计算最终结果
set @s2=@#select @amt=@#+@formula
exec sp_executesql @s2,N@#@amt int out@#,@amt out
go

--计算利润明细资料的
create proc p_qry
@code varchar(10),  --要计算的部门代码
@date datetime      --计算的日期
as
set nocount on
create table #t(code varchar(10),date varchar(10),[desc] varchar(10),amt int,seq int)

declare @dt varchar(10),@desc varchar(10),@amt int,@formula nvarchar(4000),@seq int

set @dt=convert(char(10),@date,120)

declare tb cursor local for
select [desc],formula,seq from means
where code=@code order by seq
open tb
fetch tb into @desc,@formula,@seq
while @@fetch_status=0
begin
 --计算公式
 exec p_calc @formula,@code,@date,@amt out
 insert #t values(@code,@dt,@desc,@amt,@seq)
 fetch tb into @desc,@formula,@seq
end
close tb
deallocate tb

select 部门代号=code,日期=date,项目内容=[desc],金额=amt from #t
go

--调用
exec p_qry @#aa@#,@#2004-10-22@#
go

--删除测试
drop table sale,dept,cost,means
drop proc p_qry,p_calc
go

/*--测试结果

部门代号       日期         项目内容       金额         
---------- ---------- ---------- -----------
aa         2004-10-22 销售收入       15000
aa         2004-10-22 销售成本       5000
aa         2004-10-22 上交利润       3000
aa         2004-10-22 净利润        7000
--*/

 

原文转自：http://www.ltesting.net