Matching Values
-
SET NOCOUNT ON
DECLARE @Values TABLE
(
Value NUMERIC(10,2)
)
/* 20 or more values that are available */
INSERT INTO @Values (Value) VALUES (15)
INSERT INTO @Values (Value) VALUES (10)
INSERT INTO @Values (Value) VALUES (21)
INSERT INTO @Values (Value) VALUES (25)
INSERT INTO @Values (Value) VALUES (26)
INSERT INTO @Values (Value) VALUES (30)
INSERT INTO @Values (Value) VALUES (30)
INSERT INTO @Values (Value) VALUES (40)
INSERT INTO @Values (Value) VALUES (41)
INSERT INTO @Values (Value) VALUES (45)
INSERT INTO @Values (Value) VALUES (50)
INSERT INTO @Values (Value) VALUES (55)
INSERT INTO @Values (Value) VALUES (60)
INSERT INTO @Values (Value) VALUES (60)
INSERT INTO @Values (Value) VALUES (65)
INSERT INTO @Values (Value) VALUES (70)
INSERT INTO @Values (Value) VALUES (75)
INSERT INTO @Values (Value) VALUES (80)
INSERT INTO @Values (Value) VALUES (85)
INSERT INTO @Values (Value) VALUES (90)
INSERT INTO @Values (Value) VALUES (95)
INSERT INTO @Values (Value) VALUES (100)
INSERT INTO @Values (Value) VALUES (105)
DECLARE @myAmount NUMERIC(12,2) SET @myAmount = 88 -- SET THIS VALUE AS VALUE TO BE FOUND
/*
I want to get values that contribute @myAmount some thing like I know It cannot be that simple as
SELECT Value FROM @Values WHERE SUM(Value) = @myAmount
Any First found values are okay
*/
Needed out put is
Value
-------
21
26
41
Regards,
gova -
If it was for a prefix number of values, it could be done. but if the logic is find any match, with any qty of numbers, then I don't think it can be done without a substancial amount of dynamic sql work.
-
It is not prefixed number of values. It can be any number of values. As I mentioned It will be 20 or more.
Added
If there is a solution for prefix number of values I will be glad to have it. I can use it like
If prefix = 20
else if prefix = 21
(It doesn't matter how big the code would be)
Regards,
gova -
I also think that at least you should supply a maximum number of factors!
it is very difficult to compute the roots for all avalable values. But if you still want to follow that path, probaly client side is the place for these operations
* Noel -
noeld
I thought of that. But I thought if I can get a logic to get combinations of ID's(After getting the values into a table variable with IDENTITY col) then I can use IN operator to get the SUM. Front End There is no SUM or IN I have to depend on Addition.
IF (SELECT Value FROM @Value WHERE ID in (5, 6, 8)) = @myValue
SELECT Value FROM @Value WHERE ID in (5, 6, 8)
Regards,
gova -
Hey govinn, even with only 20 numbers, you have
2 432 902 008 176 640 000
different possible combinaisons of numbers.
That's 2.4 billions million combinaisons. Do you really want the server to generate that result set and then start calculating which ones add up to @x???
-
well for a fixed number of values you may try to implement Ruffinis' method for polinomial root detection. I have to brush up my math!
and once you have that see if it is possible to iterate for different values and see if you get a match.I think that your problem is better tackled from the desing rather than to use SQL to develop an algorithm
* Noel -
I have 0 advanced maths formation... I'll let you handle this one
.Btw, my number of combinaison was only for different combinaisons of 20 numbers. Now you must add the combinaisons of 19/18/17.... which grows almost exponientially at this point.
-
Question: Can you guarantee that the number you are seeking will always be a combination of the values in your table?
Mike
-
Remi I told the requester for 20 we will have 999, 999, 999, 999, 999, 999, 99 combinations. I just made this and displayed how long it takes to go to the loop.
DECLARE @AA NUMERIC(25,0) SET @AA = 9999999999999999999
SELECT GETDATE()
WHILE @AA > 0
BEGIN
SET @AA = @AA - 1
END
SELECT GETDATE()
It took forever. I had to stop. But they need a solution since they give me the paycheck. They will not accept a no until they see real logic takes lot of time.
If I get a logic to list all combinations of ID's That would be great.
Regards,
gova -
No Michael. If it does'n match any I would return no rows or a message no match
Regards,
gova -
I think you'll have to find someone with strong maths skills to help you on this one. This one is not really about t-sql skills...
Could they accept a system where the program would try to find match with only 1 value, then send results, then 2 values, report back again.
Would this eventually includ other operations (like -*/)??
-
Remi that we have to handle our selves.
Try to find a match with only one value if not then with combinations of 2 then combinations of 3 ... When we find a match Bingo. I am struggling with my Math just to get combinations beyond 3
Regards,
gova -
Like I said before, Don't try to reinvent the wheel!
Find your self a book of polynomial root detection and you will save plenty of time!!!
but if you plan to use "brute force" this is just THREE loops on client side!!!!
Oh, and BTW for loops are equivalent to SUM without any back and forth in the network!
* Noel -
Simple... but I just hope that they have a monser of a machine to make all those calculations
.
Viewing 15 posts - 1 through 15 (of 101 total)
You must be logged in to reply to this topic. Login to reply