Finding Closest Match (integer)

I am writing a program that will match two entries based on weight within 2 lbs.  I have a table with the following structure:

entry#    weight
1            135
2            137
3            154
4            190
5            175
6            188
7            193
8            177
9            210
10          215
11          235
12          238
13          240

Each entry can only be matched up against one other entry...no duplicates.  Weight must be within 2 lbs (either above or below) and can not exceed 2 lbs.  All entries left over (no match found) will be listed as "no match".  I am writting this in VB.NET and SQLLite b/c the program will need to be portable. This is a Windows form.

From above
entry 1 matches entry 2
entry 3 No Match
entry 4 matches  entry 6
entry 5 matches entry 8
entry 7 no match
etc...

Where I'm stuck is in the weight matching to 1) find a match within 2 lbs (up or down so maybe that's 4lbs ??) 2) preventing the same entry from matching itself (like on a join).

What's my best options for this task?
azyet24Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
azyet24Connect With a Mentor Author Commented:
I'm making progress but this has gotten a bit more difficult now b/c I need to now introduce teams into the code.  Meaning, I still need to match up weight but team members can't be matched against each other.  

In my code below I am successful with matching all records when ignoring teams, but from here I'm not sure how I'd keep up with multiple entries from same team.  

Here's my code so far:

Dim sql As String = "select id,team,entry,band,weight from entries order by weight asc"

 For Each Dr As DataRow In ds.Tables(0).Rows
                Dim team As String = Dr(1)
                Dim entry As String = Dr(2)
                Dim band As String = Dr(3)
                Dim weight As String = Dr(4)

                If counter > 0 Then ' first record would not have previous to compare so we skip it
                  
                    If (weight - previousweight <= 2) Then
                        'Add previous and current record to dgassignment as a match
                        dgAssignments.Rows.Add(previousentry, previousband, (Int(previousweight / 16)).ToString & "lbs " & (previousweight Mod 16) & "oz", entry, band, (Int(weight / 16)).ToString & "lbs " & (weight Mod 16) & "oz")
                        'clear previous since both prev and current are match
                        previousteam = 0
                        previousentry = 0
                        previousband = 0
                        previousweight = 0
                    Else
                        'Add previous to no match datagrid
                        dgNoMatch.Rows.Add(previousteam, previousentry, previousband, (Int(previousweight / 16)).ToString & "lbs " & (previousweight Mod 16) & "oz")
                        
                         'Add current entry as previous to be used in next loop
                        previousteam = team
                        previousentry = entry
                        previousband = band
                        previousweight = weight
                    End If
                Else
                    previousteam = team
                    previousentry = entry
                    previousband = band
                    previousweight = weight
                End If
                counter = counter + 1
            Next

Open in new window

0
 
CodeCruiserCommented:
I think one way of achieving this would be to sort the list. This way, you can match each enter up and down until the difference is more than 2.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
One of the issues that you have is that there's not enough separation between the first two weights.  A weight of 136 matches both entry 1 and 2.
SELECT entry# FROM {tablename}
WHERE weight >= MyWeight -2
  AND weight <= MyWeight + 2;

Open in new window


That should do it, once you resolve the overlap between 1 and 2.  And SQLLITE will handle that just fine...



Good Luck,
Kent
0
 
azyet24Author Commented:
Kent, so if I sort the list ASC and then set the first value as MyWeight I can see the first iteration to match up with the second record.  So I would then set MyWeight as the 3rd record and proceed unti it finds one.  So I guess I'd need to store records that didn't match in an array so that I'd know the ones that were not a match (there could be hundreds of records in list).  Is that what you had in mind by your MyWeight reference?
0
All Courses

From novice to tech pro — start learning today.