GoalSeek in SQL Server?

2 Sep 2005 7:52 PM

carmaboy

Has anyone create a GoalSeek function similar to that of Excel? I've
been researching this and trying to script one out myself without much
sucess. If someone could tell me that its impossible, that would be
helpful too. TIA.

2 Sep 2005 8:41 PM

--CELKO--

This is not really what SQL is used for.

2 Sep 2005 8:44 PM

David Portas

You may be able to do it in MDX using multiple-pass calculations to iterate
to the required result:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdmad/agmdxadvanced_6jn7.asp

In pure SQL you could perhaps do it without iteration if the range of
possible values is small enough:

SELECT TOP 1 N1.num, N2.num
FROM some_numbers AS N1, some_numbers AS N2
WHERE (N1.num + (N2.num/2) - 123) = 12345
ORDER BY N1.num, N2.num

--
David Portas
SQL Server MVP
--

4 Sep 2005 12:51 PM

Damien

I have used SQL to solve number puzzles, just for fun (eh!?). Try the
following script.

-- Easy example, all combinations of the numbers 1-9 which add up to 11
DECLARE @control TABLE ( control_no TINYINT PRIMARY KEY, control_str AS CAST
(control_no AS CHAR(1) ) )
DECLARE @i    INT

SET NOCOUNT ON

SET @i = 0

-- Add control numbers to temp table
WHILE @i Between 0 And 9
BEGIN
    INSERT @control VALUES( @i )
    SET @i = @i + 1
END

SET NOCOUNT OFF

-- List all combinations which add up to 11
SELECT a.control_no, b.control_no, a.control_no + b.control_no
FROM @control a, @control b
WHERE a.control_no + b.control_no = 11

-- Harder example; MENSA-type substitution puzzle
-- Mensa's Number Puzzles for Math Geniuses by Harold Gale has this puzzle:

--
-- Number Puzzle 58
-- Place six three digit numbers of 100 plus at the end of 685
-- so that six numbers of six digits are produced [like 685123].
-- When each number is divided by 111 six whole numbers can be found.
SELECT
    a.control_str,
    b.control_str,
    c.control_str,
    a.control_str + b.control_str + c.control_str AS result,
    '685' + a.control_str + b.control_str + c.control_str AS result2,
    CAST( '685' + a.control_str + b.control_str + c.control_str AS DECIMAL ) /
111 AS result2

FROM @control a, @control b, @control c
WHERE a.control_str + b.control_str + c.control_str > 100
AND ( CAST( '685' + a.control_str + b.control_str + c.control_str AS
DECIMAL ) / 111 ) =
    CAST ( CAST( '685' + a.control_str + b.control_str + c.control_str AS
DECIMAL ) / 111 AS INT )
ORDER BY 1

-- This puzzle is similar to ones like this:
-- How many three digit numbers are divisible by 17?
-- Well, the first one is greater than 100. 100/17=5.88235. . . So the first
is 6x17=102. We don't need to list these three digit numbers. The last one is
less than 1000. 1000/17=58.8235. . . So our three digit numbers are 6x17,
7x17, 8x17, . . ., 58x17. There are 53 such numbers.
SELECT
    a.control_str,
    b.control_str,
    c.control_str,
    a.control_str + b.control_str + c.control_str AS result,
    CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) / 17 AS
result2

FROM @control a, @control b, @control c
WHERE a.control_str + b.control_str + c.control_str > 100
AND ( CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) /
17 ) =
    CAST ( CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) /
17 AS INT )
ORDER BY 1

-- etc

Obviously the code above is quick and dirty, but you see where I'm going.
If not, look up 'Cartesian products'. Don't go using it in production
environment!

Let me know hot you get on.

Damien

Show quote

"carma***@gmail.com" wrote:

> Has anyone create a GoalSeek function similar to that of Excel? I've
> been researching this and trying to script one out myself without much
> sucess. If someone could tell me that its impossible, that would be
> helpful too. TIA.
>
>

4 Sep 2005 2:06 PM

--CELKO--

Did you notice that you wind up generating all possible combinations

5 Sep 2005 8:35 AM

Damien

Yes, I'm using the cartesian product to get the answers to the puzzles. Is
that what you mean? Presumably there are other ways to do this, but this was
just a demo, and hey it works!

Damien

Show quote

"--CELKO--" wrote:

> Did you notice that you wind up generating all possible combinations
>
>

5 Sep 2005 2:10 PM

David Portas

Perhaps Joe's point was that declarative SQL solutions generally will
produce the total set of results, effort that is highly redundant if
all solutions are equal. What you probably wanted was just the fastest
single solution. That's why SQL is likely a heavily inefficient
solution - ok for solving recreational problems but in a commercial
environment you'll probably be better off with a procedural language or
a math/stats package.

--
David Portas
SQL Server MVP
--

5 Sep 2005 8:18 PM

--CELKO--

>> Perhaps Joe's point was that declarative SQL solutions generally will produce the total set of results, <<

In fact, I just a did a short piece for DBAzine with a simple bin
packing problem (one bin, n-items) to demonstrate how the answer space
keeps doubling.