I need some help with Logic.

[vbpadawan]

I have a table of loan amounts. There are times when I need to find the group of loans that total or come closest to an amount I specify such as $2,000,000. I am trying to come up with logic to do this and need some ideas.
Thanks.

 

[SonOfEmidec1100]

I would probably do it in a sql stored proc something like this (untested).

CREATE PROCEDURE Mysp_GetLoanDetails @CheckValue MONEY AS

SELECT MyTable.* FROM MyTable INNER JOIN
(SELECT TOP 1 LoanID, ABS(SUM(Amount) - @CheckValue) AS x
FROM MyTable
GROUP BY LoanID
ORDER BY x) A ON A.LoanID = MyTable.LoanID


Maybe this will help ?

 

[tsh73]

vbpadawan,
I believe this is a problem known in math as
"knapsak problem".
(BTW, it is known to be computationally hard, if I remember right)
So you can try to search for algorythm by this title.

 

[MichaelRed]

Other 'issues' abound within the basic context, such as wheeather the sum should use a preferential ordering or grouping from the potential set. This (specific item) may appear as a desire for the fewest (or most) mnumber of elements in the set, be associated with (or NOT associated with) a specific region (including perhaps a 'preferences list?). Other elements / characteristics may be part of the selection process, however once the 'characteristics' are well defined, a simple running sum of the ordered recordset is sufficient.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places