Data Comparison of Records in Same table

Hello, This is a tough one for me anyway. I have a set of tables, and in the tables I have versioned data - e.g. I create a complete copy of the set of data and increment the version number. I then make modifications to some of the fields. I would like to be able to compare one version to another version. In the case where there is no data on the earlier version - I would like to display 'N/A', in the case where there is data in the earlier version and not the more recent version - I would like to display 'DELETED', in the case where there is data in both - I would like to display the column name, previous value and current value.

Thanks for the help
Who is Participating?
rchiversConnect With a Mentor Commented:
not sure of a couple of things like what your key columns are to link the tables but you could do something like this.
on a window create two invisible datawindows dw1, dw2
use the same dataobject on both of them.
store the data and column names in arrays. Loop through them doing the comparison
this script pops up a message box if the values are different, you could store them in a table or whatever you want.

any la_data1[], la_data2[]
string ls_col [], ls_string
integer li_count, i
//retrieve the two datawindows based on your key values

dw1.retrieve ()
dw2.retrieve ()
// find out how many columns are in the datawindow
li_count = integer(dw1.Object.DataWindow.Column.Count)
// load column names in string array
for i = 1 to li_count
      ls_string = '#' + string (i) + '.name'
      ls_col [i] = dw1.describe (ls_string)

//store the values from datawindows in the arrays
la_data1 = [1]  // store values from dw1 row 1 in array 1
la_data2 = [1]  // store values from dw2 row 1 in array 2

// check to see if both datawindows have the same number of columns
if upperbound (la_data1) <> upperbound (la_data2) then
      Messagebox ("Error","Column count mismatch")
end if
for i = 1 to upperbound (la_data1)
      if la_data1 [i] <> la_data2 [i] then
           messagebox ("Data Difference Found","Column " + ls_col [i] + '~n~r' +&
                                                                 'Value 1 = ' + string (la_data1[i]) + '~n~r' +&
                                                                 'Value 2 = '  + string (la_data2[i]))
      end if
michaelstoffelConnect With a Mentor Commented:
I'd do the comparison in SQL since it is better suited for set based operations.  You don't specify a database, so I'll assume SQLServer 2000.  This is not tested and it's been awhile so the syntax is probably off, but you'll get the idea

Table( version, key, field)

                          CASE older.key IS NULL THEN 'Inserted'
                          ELSE 'Modified'
            END CHOOSE AS Action,
            older.field AS OldValue,
            newer.field AS NewValue
FROM Table newer
   ON newer.key = older.key
WHERE newer.version = :newVersionArg
  AND older.version = :oldVersionArg

Make a datawindow based on the above SQL and pass the version args
All Courses

From novice to tech pro — start learning today.