[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Calculate a Math formula in a column

Posted on 2009-02-12
27
Medium Priority
?
3,275 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
0
Comment
Question by:HoggZilla
  • 8
  • 8
  • 5
  • +2
27 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 23624212
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

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23624299
mwv: Where this will break is if the forumla can be different for various columns.  
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 23625076
?? 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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23625588
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.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 23625700
>>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.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 800 total points
ID: 23625902
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.
0
 
LVL 17

Author Comment

by:HoggZilla
ID: 23625970

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.
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 400 total points
ID: 23626152
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

0
 
LVL 17

Author Comment

by:HoggZilla
ID: 23626643
Thanks, I am off for the day but will look at this later this evening. Really appreciate all of your help and ideas.
Hogg
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23626674
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)).
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 800 total points
ID: 23632831
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 ?



0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23632907
ooopppssss - didn't see the datatable being seperate - must read more carefully he says sheepishly.... I'll be back
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23632927
>> 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.).]
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 800 total points
ID: 23633029
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'

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23633054
Just working on adding in the brackets now - wasn't there in the old one...
0
 
LVL 17

Author Comment

by:HoggZilla
ID: 23633193
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.

0
 
LVL 17

Author Comment

by:HoggZilla
ID: 23633285
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23633309
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 "+"  "-"  "*"  "/"
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23633349
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).

0
 
LVL 17

Author Comment

by:HoggZilla
ID: 23633473
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.
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 800 total points
ID: 23633479
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.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 23633510
>> 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 -- :-( :-( .
0
 
LVL 17

Author Closing Comment

by:HoggZilla
ID: 31546159
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
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23634507
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...
0
 
LVL 17

Author Comment

by:HoggZilla
ID: 23634623
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!!!
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 23634644
Might post without the brackets, and then work from there :)
0
 
LVL 17

Author Comment

by:HoggZilla
ID: 23634666
Mark, Go to bed! :-)
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question