|
| |
|
|
and13345
Posts: 100
Joined: 10/7/2003
Status: offline
 |
Comparing similar records in the same database - 10/22/2007 13:52:10
I have a database that has many records. To simplify the example I have the following fields.
ID, Build_Number, Parameter, Par_Value. The field "ID" is unique and is assigned numerically when the records are entered. Build_Number will be a number that several records in the database have the same number. Parameter is a value that is unique for each Build_Number and Par_Value is just a value for the specific Parameter
What I want to do is compare the Parameter fields for two different Build_Numbers. So I will create a page where I will enter the first Build_Number I want to use and a second field with the second Build_Number I want to use. Between the two builds about 95% of the records will have the same Parameters. The other 5% of the records will be made up of records that will have a Parameter value for one of the builds, but the other build will not have that same parameter value.
So as an example I have these records in a database.
ID Build_Number Parameter Par_Value
1 100B00 Test_Par1 1
2 100B00 Test_Par2 A
3 100B00 Test_Par3 B
4 900B00 Test_Par1 1
5 900B00 Test_Par2 C
6 900B00 Test_Par4 X
From these records I want a table that is similar to this
Line # Parameter 100B00 Compare Value 900B00 Compare Value Value
1 Test_Par1 1 1 Same
2 Test_Par2 A C Different
3 Test_Par3 B Only found in one Build_Number
4 Test_Par4 X Only found in one Build_Number
Notice that Line 3 only has a value under 100B00 Compare Value and Line 4 only has a value under the 900B00 Compare Value
This table will allow me to know if the value for a specific Parameter is in both Build_Numbers or if one Build_Number contains a Parameter that the other Build_Number does not. When the Parameter is in both Build_Numbers I want to compare the values and note if they are the Same or Different.
Hopefully I haven't made this too confusing.
|
|
|
|
and13345
Posts: 100
Joined: 10/7/2003
Status: offline
|
RE: Comparing similar records in the same database - 10/22/2007 13:54:54
I should have made it a little more clear what I was needing help on. I am not sure what is the best way to collect the data to do the comparison. I was thinking I would have to read the records that match the Build_Numbers I specified in to a temporary database where I would match like parameters up and put them in the same record and then perform my comparison from there, but I am not sure if that is the best approach.
| |
|
|
|
rdouglass
Posts: 9280
From: Biddeford, ME USA
Status: offline
|
RE: Comparing similar records in the same database - 10/22/2007 16:23:14
I would create 2 arrays. Read each set of data into the arrays and use 1 as a "control" set.
Are you familiar with arrays?
I would also use some 'flags' along the way to designate whether it was found and what the found status was. Then I'd do something like this:
FOR i = 0 TO ubound(myFirstArray,2) 'start first loop
'set the default state of "no match found"
myTempStr = myFirstArray(2,i) & " Only found in one Build_Number"
FOR j = 0 TO ubound(mySecondArray) 'start second loop
'assume the 2nd column in each data row is the parameter field and the 3rd is the value field
IF trim(myFirstArray(1,i)&"") = trim(mySecondArrayArray(1,j)&"") THEN
'if the parameter items match, check the value
IF trim(myFirstArray(1,i)&"") = trim(mySecondArrayArray(1,j)&"") THEN
myTempStr = mySecondArray(2,ji) & " Same"
ELSE
myTempStr = mySecondArray(2,j) & " Different"
END IF
END IF
NEXT
Response.write(myArray(0,i) & " " & myArray(1,i) & " " & myArray(2,i) & " " & myTempStr
NEXT
Now I just kinda' threw this together and did not confirm for syntaxt but this is probably how I'd approach it.
See, what I'm doing is grabbing each item from the first array and looping thru the second array for a parameter match. If I find a parameter match, check the values and set the 'myTempStr' variable accordingly. (Notice we pre-set it for no-match before we start the comparison for each row of the first array.)
The one downfall of doing it this way is finding the ones "Only found in the second array". You could run this script again reversing roles (array 1 becomes array 2) and only response.write the items that are only in the first array (which was the second array the first time thru.)
Did any of that make sense? Of course, you need to populate the arrays first. But to be honest, if you aren't already familiar with arrays, this script may be challenging.
Hope it helps.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
and13345
Posts: 100
Joined: 10/7/2003
Status: offline
|
RE: Comparing similar records in the same database - 10/25/2007 17:42:47
I am familiar with array's, but they are not very friendly when it comes to sorting. I need to be able to manipulate the order for reporting purposes. I decided to take the approach of reading the necessary records in to an ADO.Recordset. I was able to use a couple of nested connections at the same time to generate the appropriate data that I placed in to a ADO.Recordset, sorted and did some comparisons within the record.
This seems to work quite well, but does take a bit longer when using a lot of records.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
