Calculation and processing of custom formulas

Original post address:
http://community.csdn.net/Expert/topic/3485/3485588.xml?temp=.8813745

--Sample data
CREATE TABLE Sale (date datetime,code varchar (), 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 (a), name varchar (10))
Insert Dept Select ' AA ', ' Chinese Restaurant '
UNION ALL select ' BB ', ' Western Restaurant '
UNION ALL SELECT ' CC ', ' housekeeping '
UNION ALL SELECT ' dd ', ' KTV Department '

CREATE table cost (date Datetime,code varchar (), 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 (a), seq Int,[desc] varchar (a), formula nvarchar (4000))
Insert means select ' AA ', 1, ' Sales revenue ', ' ^sale@amt^ '
UNION ALL SELECT ' AA ', 2, ' cost of sale ', ' ^cost@amt^ '
UNION ALL SELECT ' AA ', 3, ' surrendered profit ', ' ([1]-[2]) *0.3 '
UNION ALL SELECT ' AA ', 4, ' net profit ', ' [1]-[2]-[3] '
Go

/*--Problem Handling Requirements Description

Write a calculation of the stored procedures, complete according to the basic data and calculation methods, the data into the profit schedule!

If the user enters the inquiry condition is the department code number AA (@code), the date is: 2004-10-22 (@date)

The processing process is as follows,
1: From the Profit calculation Method table (means) Read code = AA to temporary table
SELECT * into #temp from means where Code = @code ORDER BY seq

2: Cycle through each item with a cursor, in the order of SEQ from small to large, as the first project
Insert into Gain_detail (Code,date,desc,amt)
Select @code, @date, @desc, Sale.amt from sale where code = @code and date = @date
Other projects Similar

Formula Description:
1.^ table name @ field name ^: For example: ^sale@amt^ represents the value of the Amt field from the sale table, and the value condition is code= @code and date= @date
2.[SEQ]: For example: [1]-[2]-[3],[] is a reference to the previous Department of the calculation results, [1] means that the sales revenue of the Department [2] represents the cost of sales, other similar
3. The other is the standard calculation expression
--*/

--Problem handling:
--Stored procedures for formula calculations
Create proc P_calc
@formula nvarchar (4000),--formula to be calculated
@code varchar (10),--Department code
@date datetime,--Calculated date
@amt int out--Results of calculations
As
declare @s1 nvarchar (4000), @s2 nvarchar (4000), @i int,@j int

--External calculation
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, @date datetime, @amt int out '
, @code, @date, @amt out
Select @formula =replace (@formula, @s1, @amt)
, @i=patindex ('%^%@%^% ', @formula)
End

--Calculating internal formulas
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

--Calculate the final result
Set @s2 = ' Select @amt = ' + @formula
EXEC sp_executesql @s2, N ' @amt int out ', @amt out
Go

--Calculation of profit details
Create proc P_qry
@code varchar (10),--Department code to be calculated
@date datetime--Date of calculation
As
SET NOCOUNT ON
CREATE table #t (code varchar (), date varchar (a), [desc] varchar, amt INT,SEQ int)

DECLARE @dt varchar (a), @desc varchar (a), @amt int, @formula nvarchar (4000), @seq int

Set @dt =convert (char), @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
--Calculation formula
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 Department code =code, date =date, project content =[desc], amount =amt from #t
Go

--Call
exec p_qry ' AA ', ' 2004-10-22 '
Go

--Delete test
drop table Sale,dept,cost,means
drop proc P_qry,p_calc
Go

/*--test Results

Department Code date Project content Amount
---------- ---------- ---------- -----------
AA 2004-10-22 Sales Revenue 15000
AA 2004-10-22 Sales Cost 5000
AA 2004-10-22 Profit 3000
AA 2004-10-22 Net profit 7000
--*/