自定义公式的计算处理

王朝other·作者佚名  2006-01-09
窄屏简体版  字體: |||超大  

原帖地址:

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

--*/

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航