Methods for identifying differences in accumulating history table?

Question

This question will probably be elementary to most DBAs.

I'm doing light DB work which isn't my specialty and I'd like to create some kind of "change log" for a table that is copy/appended to a history table each morning. The table is being created in the fashion below, where the entire contents of one table is being appended to a history table with a column on the end called backup_date.

I wrote some SQL to catch new records and deleted records from the previous day but what is the best method to determine what records have changed?

For example: on March 9th, Mr. Gates told HR that he prefers to go by "Bill" instead of "William". Data for March 10th shows that HR made a change to his first name. What is the best method of attack to compare to previous data in the table? I'm mostly interested in comparing to the previous day (each day) but what if I wanted a list of all changes to a particular record?

Answer 1

Using your current method you might use a query such as the one below. This example uses derived tables to treat each backup date range as if it where a table. Then joins two backup date derived tables (named Today and Previous) on the emp# (I have used EmpNo in my example) and uses the WHERE clause to get only records with at least on difference between the two. Note that the syntax for derived tables may vary depending on what database product you are using. Adding the database product to the question might help the question be answered more accurately.

SELECT 
    Today.EmpNo
    , Today.First AS TodayFirst
    , Today.Last AS TodayLast
    , Today.DOB AS TodayDOB
    , Previous.First AS PreviousFirst
    , Previous.Last AS PreviousLast
    , Previous.DOB AS PreviousDOB
FROM
    (
        SELECT *
        FROM employeehist
        WHERE BackupDate = '3/10/2014'
    ) AS Today


    INNER JOIN 
    (
        SELECT *
        FROM employeehist
        WHERE BackupDate = '3/9/2014'
    ) AS Previous
        ON ( Today.EmpNo = Previous.EmpNo )


WHERE  ( Today.First != Previous.First OR Today.Last != Previous.Last OR Today.DOB != Previous.DOB );

To find all versions of a particular employee record use DISTINCT. This will return unique versions of each record.

SELECT DISTINCT First, Last, DOB
FROM employeehist
WHERE EmpNo = 1;

You might also consider creating a trigger on the employee table which will only write records to the history table when changes are made. This would allow you to track all edits to an employee without having to compare each backup version to every other backup version.