We help IT Professionals succeed at work.

Calculate a Math formula in a column

Medium Priority
4,026 Views
Last Modified: 2012-06-27
I have a table that holds formulas, for example [AMT]*[FX_RT]*[VAT_RT]. I need to select from this table and add a new column containing the results. All of the bracketed values in this "formula" column are references to actual columns on the table. So there is an AMT, FX_RT and VAT_RT column on this table.

SELECT '10000*.875*.0524879' FORMULA, CONVERT(MONEY,10000*.875*.0524879,0) AS I_NEED_THIS_COLUMN

Thanks
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
You will need to do this as dynamic SQL as far as I know.

DECLARE @SQL NVARCHAR(MAX)
 
SELECT @SQL = 'SELECT ''' + FormulaColumnName + ''' AS FORMULA, CONVERT(MONEY, ' + FormulaColumnName + ',0) AS RESULT FROM DataTableName' 
FROM FormulaTableName
-- Use where clause to filter to only one formula if multiple
--WHERE SomeColumn = 'SomeCondition'
 
EXEC(@SQL); -- execute dynamic sql

Open in new window

mwv: Where this will break is if the forumla can be different for various columns.  
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
?? Not sure it will, but if you say so.

The SQL I am constructing should come out something like this:

SELECT '[AMT]*[FX_RT]*[VAT_RT]' AS FORMULA
, CONVERT(MONEY, [AMT]*[FX_RT]*[VAT_RT],0) AS RESULT
FROM DataTableName

As long as the formula has valid column names and mathematical operators that the above SQL query would run correctly then NOT sure what the issue would be.  Would have to defer to Brandon on an example.

Regards,
Kevin
But I believe that the Formula is, if I understand correctly, a column within the table containing the data.  So there's probably not a formula table, but the formula is contained within the "Formula" column.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
>>I have a table that holds formulas, for example [AMT]*[FX_RT]*[VAT_RT]. I need to select from this table and add a new column containing the results.

Ok, i see what you are saying now, Brandon.  I was going off of the above.  And my interpretation was that table holding formula had string data that represented column names and mathematical operators for data in some other table that has columns in this example of [AMT], [FX_RT], and [VAL_RT].  If this is in the same table, then should still work with dynamic sql; however, that wouldn't work if these formulas are in computed columns if that is what you are meaning Brandon.
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
If the total number of different columns in the formula is relatively limited, I suggest using a function to calc the result.

Since you're in SQL 2005, you can use CLR function, and just pass all columns to the function so it has every possible value it would need to resolve the formula.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
CERTIFIED EXPERT

Author

Commented:

Yes, correct. Here is exactly what I have.
I have a table with data, for example, AMT, FX_RT, VAT_RT, etc. The table also contains 3 other elements: A Project, a InvCode and a TypeCode. Based on these 3 values I can join to my "Formula" table. For every match in he Formula table, calculate the result. So for example. Project 'AA', InvCode 'D' and TypeCode 'Q' has 3 Formula rows.
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Something like this maybe.  Like Scott's approach if you have choice to design differently if you don't like the dynamic sql approach.
DECLARE @SQL NVARCHAR(MAX)
 
SELECT @SQL = ISNULL(@SQL + ' UNION ALL ', '') 
	+ 'SELECT ''' + f.FormulaColumnName + ''' AS FORMULA, CONVERT(MONEY, ' + f.FormulaColumnName + ',0) AS RESULT FROM DataTableName' 
FROM FormulaTableName f
INNER JOIN DataTableName d ON d.Project = f.Project AND d.InvCode = f.InvCode AND d.TypeCode = f.TypeCode
 
EXEC(@SQL); -- execute dynamic sql

Open in new window

CERTIFIED EXPERT

Author

Commented:
Thanks, I am off for the day but will look at this later this evening. Really appreciate all of your help and ideas.
Hogg
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
It's fairly easy to get from :
[AMT]*[FX_RT]*[VAT_RT]
to:
'10000*.875*.0524879'

The trick is how to evaluate that string as an expression and return the result.

That will likely be MUCH easier in a CLR function than in SQL Server itself.

I know, for example, a lot of people use an EVAL() in VB to do it (at least I used to use one I created back in worked (a LITTLE) with VB (and even back in QuickBasic days)).
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Why all the discussion ? it is a clear case of dynamic SQL, just not that way... maybe... And why the frig MS have not accommodated a selectable proc or call procs from functions, or dynamic sql in functions will always remain a mystery to me - and yet there is CLR - developers will write something outside, and DBA's probably won't. Considering the company, thought is was ok to vent a little bit...


This will also work :

declare @sql varchar(max)

select @sql = isnull(@sql,'') + case when @sql is NULL then 'select ' else '
union all
select ' end +formula+' as formula_answer, * from formulatablename where formula = '''+formula+'''' from formulatablename group by formula

exec(@sql)


But if very large, or lots of different formula then using SQL means either a cursor, or a while loop, and really is best being a CLR function to do the equivalent of the VB EVAL() function or VB EXECUTE() function - And I can do that in SQL (yeah wrote a maths parser ages ago) the trouble being replacing column names with values in the formula. Might play with that a bit more - are they any rules on the formula like number of columns, brackets etc ?



Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
ooopppssss - didn't see the datatable being seperate - must read more carefully he says sheepishly.... I'll be back
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> the trouble being replacing column names with values in the formula <<

Actually, to me, that's the *easy* part, as I said.

The *hard* is finding an efficient way to evaluate the resulting expression.  I don't know of how to do that easily in SQL.

[I STRONGLY wish SQL would an EVAL() function: I do *not* need it to support variables or group functions (AVG(), etc.), just arithmetic expressions and basic functions (SQRT(), ROUND(), etc.).]
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
They have the rotten thing every where else... It is a VB / VBA function, so could not be too hard to supply the function to SQL - surely.

So, what if I was to give you a function that can do things like:   select dbo.udf_Maths(mystr)     -- where mystr = '1+3*(5-1)/8'

Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Just working on adding in the brackets now - wasn't there in the old one...
CERTIFIED EXPERT

Author

Commented:
I think the UDF is the approach that is generic enough. Like was mentioned earlier by Scott, this is the perfect description of the gap. I would consider CLR if it was not like driving a truck through a china shop to get it done at my company.
It's fairly easy to get from :
[AMT]*[FX_RT]*[VAT_RT]
to:
'10000*.875*.0524879'

The trick is how to evaluate that string as an expression and return the result.

CERTIFIED EXPERT

Author

Commented:
So, what if I was to give you a function that can do things like:   select dbo.udf_Maths(mystr)     -- where mystr = '1+3*(5-1)/8'
That is the only thing I see that will work for my issue. I am going to end this question and open a new one for the UDF code.
Thanks All!
Hogg
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Are there any brackets ? :) Them there things are damned tricky, but on a mission now.

I am not quite as confident about the ease of replacing strings within a column with their value equivalent - can use a biggish "case" but I am imagining that there is array logic lurking to recurse the column to extract component columns and reassemble with string values and operators.

By the way, at the moment, I can only handle simple operators "+"  "-"  "*"  "/"
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
No, do not end this one, just post the code to do the interpretaion of the formula from column names into string values.

More than happy to answer an SQL EVAL() function seperately, but feel that it should be kept together - besides I need more time to finish (or kill the bracket option).

CERTIFIED EXPERT

Author

Commented:
OK, I opened the new question.
Are there any brackets ? :) Them there things are damned tricky, but on a (LOL) mission now.
I don't think I need brackets for this project, but I see value in the use of them - as do you I am sure.

I am not quite as confident about the ease of replacing strings within a column with their value equivalent - can use a biggish "case" but I am imagining that there is array logic lurking to recurse the column to extract component columns and reassemble with string values and operators.
I will replace the formula with Numeric values before calling the function. Is that your question/dilemma here?

By the way, at the moment, I can only handle simple operators "+"  "-"  "*"  "/"
Great! That is awesome.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
In this case, the column names are all known, so I would think you just do something like this:


SELECT    
    'CAST(' +   -- final CAST to bring the overall result down to the desired output size
    'CAST(1.0 AS DECIMAL(18,6)) * ' + -- OPTIONAL: just to force a certain degree of accuracy during calcs
    REPLACE(REPLACE(REPLACE(f.formula,
        '[AMT]', CAST(v.[AMT] AS VARCHAR(30))),
        '[FX_RT]', CAST(v.[FX_RT] AS VARCHAR(30))),
        '[VAT_RT]',  CAST(v.[VAT_RT] AS VARCHAR(30)))
        --...all other columns...
    +  ' AS DECIMAL(10, 2))'  -- desired output size of final result
FROM formulas f
INNER JOIN values v ON f.Project = v.Project AND
    f.InvCode = v.InvCode AND f.TypeCode = v.TypeCode
ORDER BY v.Project, v.InvCode, v.TypeCode


Naturally this code could be generated as dynamic SQL so that new column(s) would automatically be included.
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
>> I don't think I need brackets for this project, but I see value in the use of them - as do you I am sure. <<

Yes, that would be the huge gain of having the SQL (or VB) engine itself do the calcs; functions would also then be available (SQRT(), etc.).

I wrote something like that (for SQL 7.0) but it required sending every value thru sp_executesql (so that SQL could do the evaluation, enabling parens, functions, etc.).  Here, naturally, that would require cursoring thru the formulas -- :-( :-( .
CERTIFIED EXPERT

Author

Commented:
A function will be my choice. Thanks to all who helped work through this one. I have opened a new question to walk through the function approach.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24141221.html#a23633856
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
I will replace the formula with Numeric values before calling the function. Is that your question/dilemma here?
Yes, but I think Scott has already achieved that and I wanted to build a generic routine and was contemplating adding a table name as an argument. But I will not worry.
I will play with brackets tonight (currently 2:30am "downunder") - might need to limit their use, and Scott, will try to consider the "ownership" of brackets for inclusion of other functions, but, you might need to jump in on including those...
CERTIFIED EXPERT

Author

Commented:
I will replace the formula with Numeric values before calling the function. Is that your question/dilemma here?
Yes, but I think Scott has already achieved that and I wanted to build a generic routine and was contemplating adding a table name as an argument. But I will not worry.

Now we are talking. :-)
That would be AWESOME!!!
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Might post without the brackets, and then work from there :)
CERTIFIED EXPERT

Author

Commented:
Mark, Go to bed! :-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.