December 10, 2013 at 6:48 am
Hi all,
I've got a bit of a puzzler that's giving me a headache, and I could do with some advice.
I've got a Calculations table that holds a formula in the syntax of
[1]+[2]+[3]+[5]/[2]
as an example, and each value is a link to a CellID in another table. What I need to do, is to replace the CellIDs in the formula with the values from the CalculationVariables Table.
Example scripts are below (ignore the CellID in the Calculations table, that's just the row in the Variables table I'll be storing the results, which is outside of the scope of this puzzle).
Create Table
#Calculations
(CalculationIDINT PRIMARY KEY,
Formulavarchar(5000),
CellIDINT
)
INSERT INTO
#Calculations
(CalculationID,
Formula,
CellID
)
VALUES
(1,
'[1]+[2]+[3]+[4]-[5]',
6)
Create Table
#CalculationVariables
(CellID INT PRIMARY KEY,
Value DECIMAL(35,10)
)
INSERT INTO
#CalculationVariables
(CellID,
Value)
VALUES
(1,2.00),
(2,4.00),
(3,6.00),
(4,8.00),
(5,10.00),
(6,0.00)
So I want the above example Formula
[1]+[2]+[3]+[4]-[5]
To end up like
[2.00]+[4.00]+[6.00]+[8.00]-[10.00]
Does that make sense?
Thanks
December 10, 2013 at 7:49 am
The way I'd approach this would to start by talking out loud the logic. What you are doing is assembling an expression then executing it. It is kind of the processing you would do with Reverse Polish Notation.
With that said, you need to parse out the indexes, values between the braces. You also need to examine the arithmetic expression (your example only shows + and -). Accommodate for all expressions necessary.
Finally, you need to process the equation, assuming left to right, applying the appropriate arithmetic expression to a running total.
That is how I'd approach the problem. From there I'd accomplish each task in code;
1) parse index.
2) parse arithmetic expression.
3) build a expression processor to calculate the result.
From here, it is all code. How you go about it is totally up to you.
Good luck.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 10, 2013 at 7:52 am
Thanks for that note, although at this time I've already got a function that will take the resultant formula and execute it to give me the result value, at this point all I'm trying to do is update the Formula and replace the CellIDs with the Values from the other table.
December 10, 2013 at 7:56 am
The next question I'd want an answer is; how many entries in #CalculationVariables? Based on that would drive the logic.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 10, 2013 at 7:59 am
It can be anywhere up to 15000 rows I think.
December 10, 2013 at 8:09 am
OK, so what the approach I'd use would be to create a table variable or temp table that contains all of the indexes and associated values found in the "formula". You would need to walk through the "formula" string pulling out all of the index references, inserting them into the temp table. Then I'd update the temp table with the corresponding value from the #CalculationVariables table.
Now that you have all of the corresponding values, I'd write a routine that walks through the temp table replacing the index in your "formula" with the corresponding value.
Once that has been accomplished, send it off to your formula processor, which you said you already have.
If someone else has a better way, please feel free to jump in.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 10, 2013 at 8:39 am
That was what I tried originally, but the performance is terrible using pattern matching on the formula, iterating through each CellID to replace it with the given value.
I was hoping a CTE or similar would be much faster, as I really want to avoid using any row based expression (either a UDF or CLR function) as I don't think it'll perform as fast as I need it to do. There has to be an easier set based approach to this, but I can't see it.
December 10, 2013 at 8:59 am
how many "formulas" need to be processed?
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 10, 2013 at 9:03 am
Anywhere from 1 to perhaps 3000 rows
December 10, 2013 at 9:43 am
Have you considered creating a UDF that returns a table of index values in the formula?
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 10, 2013 at 11:00 am
Maybe something like this?:
declare @replace1 varchar(max)
declare @replace2 varchar(max)
select @replace1 = (
select 'REPLACE('
from #CalculationVariables
for xml path('')
)
select @replace2 = (
select ', ''[' + CAST(CellID AS varchar(10)) + ']'', ''' + CAST(Value AS varchar(50)) + ''')'
from #CalculationVariables
for xml path('')
)
select @replace1 = 'select *,' + @replace1 + 'Formula' + @replace2 + ' as Formula_With_Values from #calculations'
print @replace1
exec(@replace1)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 10, 2013 at 11:09 am
For performance reasons, it would almost certainly be better to have a trigger on the Calculations table that automatically identified and saved all variables used in a normalized table whenever a formula was added/changed.
You could then use that table to generate only the specific REPLACE(s) needed for each formula.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 10, 2013 at 11:29 am
ScottPletcher (12/10/2013)
For performance reasons, it would almost certainly be better to have a trigger on the Calculations table that automatically identified and saved all variables used in a normalized table whenever a formula was added/changed.You could then use that table to generate only the specific REPLACE(s) needed for each formula.
I agree... It is certainly the smaller of the result-set of data to process.
Kurt
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply