Computing 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 (10), amt int)
Insert sale select '2014-10-22 ', 'A', 2004
Union all select '2014-10-22 ', 'bb', 2004
Union all select '2014-10-22 ', 'cc', 2004
Union all select '2014-10-23 ', 'A', 2004
Union all select '2014-10-23 ', 'bb', 2004
Union all select '2014-10-23 ', 'cc', 2004

Create Table dept (Code varchar (10), name varchar (10 ))
Insert dept select 'A', 'Chinese restaurant'
Union all select 'bb', 'Western restaurant'
Union all select 'cc', 'room Department'
Union all select 'dd', 'ktv part'

Create Table cost (date datetime, code varchar (10), amt int)
Insert cost select '2014-10-22 ', 'A', 2004
Union all select '2014-10-22 ', 'bb', 2004
Union all select '2014-10-22 ', 'cc', 2004
Union all select '2014-10-23 ', 'A', 2004
Union all select '2014-10-23 ', 'bb', 2004
Union all select '2014-10-23 ', 'cc', 2004

Create Table means (Code varchar (10), seq int, [DESC] varchar (10), formula nvarchar (4000 ))
Insert means select 'A', 1, 'sales revenue ',' ^ sale @ AMT ^'
Union all select 'A', 2, 'sales cost', '^ cost @ AMT ^'
Union all select 'A', 3, 'turn in profit ',' ([1]-[2]) * 0.3'
Union all select 'A', 4, 'net profit ',' [1]-[2]-[3]'
Go

/* -- Description of the problem handling Requirement

Write a computing stored procedure and put the data into the profit list based on the basic data and calculation methods!

For example, if you enter the Department Code AA (@ Code) and the date is 2004-10-22 (@ date)

The process is as follows,
1: Read code = AA from the profit calculation method table (means) to the temporary table
Select * into # temp from means where code = @ code order by seq

2: process each project cyclically with a cursor in the ascending sequence of seq, such 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 are similar

Formula description:
1. ^ table name @ field name ^: for example, ^ sale @ AMT ^ indicates that the AMT field value is obtained from the sale table. The value can be code = @ code and date = @ date.
2. [seq]: for example, [1]-[2]-[3], [] refers to the calculation result item before the Department, [1] indicates the sales revenue of the department. [2] indicates the sales cost.
3. Others are standard computing expressions.
--*/

-- Troubleshooting:
-- Stored procedure of formula calculation
Create proc p_calc
@ Formula nvarchar (4000), -- formula to be calculated
@ Code varchar (10), -- department code
@ Date datetime, -- Date of Calculation
@ AMT int out -- Calculation Result
As
Declare @ S1 nvarchar (4000), @ S2 nvarchar (4000), @ I int, @ J int

-- External computing
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

-- Calculate the internal Formula
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

-- Calculate 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 (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
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 'A', '2017-10-22'
Go

-- Delete test
Drop table sale, DEPT, cost, means
Drop proc p_qry, p_calc
Go

/* -- Test Result

Department Code date project content amount
-----------------------------------------
AA 2004-10-22 sales revenue: 15000
AA 5000-10-22 sales cost
AA 3000-10-22 turning in profit
AA 7000-10-22 net profit
--*/