Comparing values between two rows of data and only showing the columns that are different

Question

In a previous application version we were using a particular field for a primary key, but because the field may represent different identities across various systems we have made it a non significant field(ie not a primary key or part of a composite primary) however since we dont have another system yet users still use that field as a primary method of identification.

The problem is with auditing...previously I used a single table to do all audits for the database dumping the data with a newvalue oldvalue schema using the generic trigger that is floating around. This could still work fine except for one thing. I have moved contactinformation into a separate table that is tied to the new primary key of the original table. So when changes are made the unfamiliar and unused primary key shows in the auditlog instead of the now insignificant foreignSystemID...

I moved to doing a one to one copy method of auditing so that any changes to any table are now written to a mirror image in a different schema. The problem comes down to showing changes to the users. They are used to seeing a report that shows only the changed values for a particular doctor...

My question would be using sql queries and Crystal reports, how could I show only the changed column values between rows in my audit tables. I have looked at the pivot command, but I dont think thats really going to help me. I had also looked at the code within the script that compares the columns and determines if they are different and writes them to the table.

im really spinning in the sand here and this is a critical issue for me to solve. Thanks in advance for ANY help...

we are early enough into production that I could change my changetracking method if need be, but it needs to be soon. thanks

EDIT:

My boss and I have worked on this a bit and this is what we have started with...I would like to get further opinions and options...as well...thanks..

CREATE TABLE #TEMP (
    DoctorsID bigint, 
    TableName varchar(50), 
    FieldName varchar(50), 
    CurrentFieldValue varchar(255), 
    PreviousFieldValue varchar(255), 
    PreviousValueDate datetime 
    )

DECLARE @sql varchar(MAX)

SELECT 
    @sql = COALESCE(@sql,'') + 
    CAST(
        'INSERT INTO #TEMP ' + 
            'SELECT ' + 
                'o.DoctorsID, ' + 
                '''' + TABLE_NAME + ''' ,' + 
                '''' + COLUMN_NAME + ''',' + 
                'o.' + COLUMN_NAME + ',' +
                'a.' + COLUMN_NAME + ',' +
                'a.AuditDate' +
            ' FROM ' + 
                'dbo.DoctorLicenses o ' + 
                    'INNER JOIN Audit.DoctorLicenses a ON ' + 
                        'o.DoctorsID = a.DoctorsID ' + 
            'WHERE ' + 
                'AuditDate BETWEEN ''10/01/2010'' AND ''10/31/2010'' AND ' + 
                'o.' + COLUMN_NAME + ' <> a.' + COLUMN_NAME + 
            ';'     
    AS varchar(MAX))
FROM 
    INFORMATION_SCHEMA.COLUMNS AS [Fields]      
WHERE 
    TABLE_SCHEMA = 'dbo' AND 
    TABLE_NAME = 'DoctorLicenses' 

PRINT @sql

EXEC(@sql)

SELECT * FROM #TEMP

DROP TABLE #TEMP

Answer 1

It sounds to me like there is a design issue, but I have a hard time envisioning what your design is at the moment. Can you be more specific on what your tables look like at the moment and what data you're trying to generate the report(s) on?

Also, when talking about auditing "the changed values", how do you keep track of what's been changed?