Need a stored procedure to find all records that is needed to make up a specific value
Posted on 2007-10-08
I'm using SQL Server 2005.
For the sake of clarity I will simplify the structrue of the information records.
I have a result set with 10 000 records. Each records has two columns, an id and a value:
ID => Unique Identifier
Value => Float
The value column can have any amount from 0.01 to 10 000 000.
What I need to do is find a number of records in this resultset that will add up to 100 000 or the closest possible value to 100 000.
EXAMPLE: (for this example I will make the ID just two characters)
KE 265 392.00
VW 15 935.04
OW 80 993.00
PR 1 324.56
In the above Example OW, VW, PR, WE, WT adds up to 99 982.52 so those are the transactions I'm interested in.
QUESTION: How do I determine which transactions to use to get as close as possible to 100 000?