T-SQL and The Excel Solver
-
We don't need to see actual data (even if it's always better to have real life data).
Can you provide a few rows of fake data and the required output you need? If you can't provide that then I don't see how I can provide more information other than what I already offered since Jeff's and my solution is giving correct results based on our current understanding of the problem... Maybe this is just because you don't know how to translate it to your environement (doubtfull but you never know).
-
Hi Remi,
Care to explain how you can expand your CASE statement to any number of columns to compare?
Justice and Remi,
The formula for determining the MIN of two numbers is "the sum of the two numbers minus the absolute value of the difference of the two numbers divided by two" and it looks like this...
((#1 + #2) - ABS(#1 - #2)) / 2
I haven't played with trying to solve an unknown quantity of columns but the formula above was what I used to solve for the MIN of 3... I used the formula once to solve for the MIN of the first two numbers and then used that result as an operand in the formula again against the 3rd number to solve for the MIN of 3. Using that same method, I imagine that a function with some form of recursion against a table variable of values would do. Self recursion would only be good to 32 numbers (nested level limits of SQL Server) but a loop against a table variable would probably do. Same would go for the CASE method you were kind enough to post. Of course, I probably wouldn't actually use a loop... I'd probably do it with a join to my old friend, the Tally table.
Just to round this whole thing out... the formula for the MAX of two numbers is very similar...
((#1 + #2) + ABS(#1 - #2)) / 2
As you saw, though, the CASE statement method is actually a bit faster, which surprised the hell out of me, but that's what I get for not testing both methods before posting. I haven't needed to ever to this in a database before...
As a side bar, I can't imagine needing the MIN or the MAX of more than 2 or 3 columns... you could make a function to do it for two numbers and just nest the function once for 3 numbers thusly ...
SELECT dbo.fnMinOfTwo(dbo.fnMinOfTwo(#1,#2),#3)
...and it could be done using either the formula or the CASE method. I think the CASE method would still win because about half the time (statistically speaking) it will find the min on the first comparison. Of course, if you absolutely know that you will always be looking for the MIN of 3, a dedicated function will be faster than a general purpose function with recurrsion.
<... pause ...>
Justice, here's an easy way out...
Well, I just did a test... turns out that the CASE method is, in fact, the faster of the two methods even when limited to just 2 operands. With that in mind, here's a dedicated function to solve for the MIN of 3 numbers (all 3 must be present)...
CREATE FUNCTION dbo.MinOf3 (@Num1 INT, @Num2 INT, @Num3 INT)
RETURNS INT
AS
BEGIN
RETURN (SELECT CASE
WHEN @Num1 < @Num2 AND @Num1 < @Num3 THEN @Num1
WHEN @Num2 < @Num3 AND @Num2 < @Num1 THEN @Num2
ELSE @Num3
END)
END
Keep in mind that this function takes about twice as long as having the code inline as we did in the examples... still, it's pretty fast (about 10 seconds to solve 500,000 rows) and makes life mighty convenient and decreases the risk of someone fat-fingering the inline code.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Lots of very helpful advice here which, unfortunately, answered the wrong question.
The question is how to duplicate complex functionality that is in a "legacy" application.
The "legacy" application is Excel's Solver tool. Solver is a data analysis/optimization tool that identifies the (set of) input value(s) needed by a given user function ("objective function") to arrive at a pre-specified target value , subject to zero or more constraints on the input variables.
The user function being exercized could be a combination of input values, constants, and formulas/functions which reference other values/formulas.
-
Thanks Richard, I think you have hit the nail on the head. I'm not really looking for coding samples or database design advice that might help us write a custom solution in house.
What I was wondering, was if anyone had already duplicated the functionality of Excel Solver in a way that could be easily used from a stored procedure. Perhaps someone has written a library of extended stored procedures or something that we could purchase.
Given that Excel and SQL Server are used by millions of people (I would think), I assume that somebody must have wanted to do this before and would therefore have a solution.
Perhaps my question would have been more appropriate in the "general programming" forum, I think posting it in the T-SQL forum has only added to the confusion.
-
I'm sorry to have to tell you this but : You look at the source code of the application and duplicate the logic on the server. If you can be more specific and have a case at hand, then I can help more but at the moment I have nothing more usefull to offer.
EDIT : Sorry but I completly missed the previous answer of before posting this one
. -
Out For Justice, your statement is clearly false.
My 3 data items have to be different fields, they have different meanings and are only related by formulae. If for example, you wanted to store an order to buy 10 widgets at 50 each plus 20 for postage, these are 3 separate fields in the database (price, quantity, postage) which are related by a formula : (10 * 50) + 20.
Your 3 columns are not "price, quantity, postage", because if somebody would ask you what is smaller - $10 or 5 items you would probably suggest to visit a mental institution.
You are not asking for minimal value in result of formula A*B+C, you are asking what's minimal - A, B or C.
And thisi is WRONG!
BTW, postage is another type of items in your order. So, it must not be stored in separate column. And you example formulae must look luk this:
(10*50) + (1*20)
Believe me, I saw invoices with 3 lines of different shipment charges.
_____________
Code for TallyGenerator -
> Given that Excel and SQL Server are used by millions of people (I would think), I assume that somebody must have wanted to do this before and would therefore have a solution.
Given that hammers and saws are used by millions of people (I would think), I assume that somebody must have wanted to hit nails wih saw before and would therefore have a guide how to do it.
_____________
Code for TallyGenerator -
Sergiy your comments would be so funny if I knew you were kidding... but I can't say I'm sure of that.
What if the requirement is to get the smallest value of 3 different formulas evaluating the same thing. We don't know what he wants so let's not jump to conclusions just yet.
As far as a version of the Excel solver on sql server, I can't say I have ever seen anything like this (even with 30K threads read). But that doesn't mean that no one ever did it. Good luck in your research.
-
I wish I knew by myself.
So many times my jokes appeared in fact not jokes at all...
_____________
Code for TallyGenerator -
Not exactly true... the original post asked specifically for the following...
The analyst has used the Excel Solver to find the lowest value of a each number in a set of 3 numbers that feed into several other calculations. We need to duplicate this process in a stored proc.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Simple solution for that :
Comment +
= Joke ! -
Maybe but it still took us a long time to figure out what was needed in that system
.I'm still not sure I relly know what he needs except an sql version of the solver...
-
What I was wondering, was if anyone had already duplicated the functionality of Excel Solver in a way that could be easily used from a stored procedure. Not even close to what the original question was... and I don't even know what Excel Solver does so I guess I'm done here.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
This is just a bit of frustration talking but it's amazing to me that the original post asked for how to solve the "Min of 3" problem and changed to a long and pretty useless thread about how to emulate the full funtionality of Excel Solver in SQL Server. Glad they finally figured out what they wanted...
Serqiy, gosh-darn-it... you were right from the very beginning on this one.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply