自定义公式的计算处理
原帖地址:
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','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
--*/
