GoalSeek in SQL Server?

Has anyone create a GoalS 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.This is not really what SQL is used for.|||You may be able to do it in MDX using multiple-pass calculations to iterate
to the required result:
http://msdn.microsoft.com/library/d...
anced_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
--|||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 i
s
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
"carmaboy@.gmail.com" wrote:

> Has anyone create a GoalS 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.
>|||Did you notice that you wind up generating all possible combinations|||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 wa
s
just a demo, and hey it works!
Damien
"--CELKO--" wrote:

> Did you notice that you wind up generating all possible combinations
>|||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
--|||>> Perhaps Joe's point was that declarative SQL solutions generally will pro
duce 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.