SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Looking For Set Based Solution to RBAR Like Problem with Comparison Of Data in Audit Table

Looking For Set Based Solution to RBAR Like Problem with Comparison Of Data in Audit Table

SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11245 Visits: 1675

UPDATE [2014.04.23]: I'v created a txt file containing rows of sample data (exported from Excel in csv format and renamed as a txt file) for this audit table issue and I've created 2 jpeg screenshots of the data in Excel highlighting what it is I am trying to do. I do hope that this better explains the problem and thus results in a few responses.

At current there are nearly 100 views and not 1 response which means I either did not orginally provide enough info to describe what I am trying to do or I'm asking for something so difficult that there is no answer. Hopefully that will change with these 3 attachments (something I now know how to do in a post on this forum).

Thank You

I've included the DDL to construct the audit table I am working with. Understand that this audit table nor the audit mechanism in place is of my own design; both are part of the accounting software system we use. Because of this the changes I can make are limited to the audit table only and even then they must be passive changes only so as to not interfere with how our primary accounting software uses the table.

The audit tables definition consists of 3 audit specific columns; dtEvent, hID and IsDuplicate. The dtEvent column is the date/Time the data was captured. The hID column is the tables primary key. The last column named IsDuplicate is a flag used to indicate if a row is duplicate of an existing row in the audit table. When IsDuplicate is a non-zero value the systems maintenace process knows its a row that can be removed. The remaining columns are all from the PERSON table that is being audited. The columns TriggerType thru Email are all found in the PERSON table which the PERSON_AUDIT table is auditing.

Whenever a change is made to a person that causes a row in the PERSON table to be updated the PERSON_AUDIT table is populated with the values from the INSERTED table after the update is done. Sometimes the data captured is the exact same as was in the table to begin with. I don’t know why this happens only that it does. When a duplicate row is inserted the IsDuplicate column is supposed to be set to a non-zero value but this stopped working recently and so I now am faced with cleaning this up manually. I thought I might could do this using aggregation by returning the Min & Max value of hID grouping on all the columns except for dtEvent & IsDuplicate however I discovered that method was sometimes flagging a row as a duplicate that was not a duplicate.

If a user changes a person’s last name from Smith to Smith no change has occurred and so if a row is added to the audit table then it is a true duplicate. If a user changes the Last Name from Smith to Black and then changes it back from Black to Smith, saving after each change, then we do not have a duplicate row where LName = Black but it looks that way to the aggregation. So I need to check each successive row, comparing the current row to the next one where hRcrd is the same and if all columns between the 2 rows are the same then I can flag the higher valued hID as a duplicate. How can this be done without going the RBAR method?

Any feedback is most welcome


[hID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
[dtEvent] [datetime] NOT NULL,
[TriggerType] [char](1) NOT NULL,
[hRcrd] [numeric](18, 0) NOT NULL,
[Code] [char](8) NOT NULL,
[LName] [varchar](256) NULL,
[FName] [varchar](255) NULL,
[Addr1] [varchar](256) NULL,
[City] [varchar](256) NULL,
[State] [varchar](5) NULL,
[Zipcode] [varchar](12) NULL,
[sPhoneNum0] [varchar](20) NULL,
[sPhoneNum1] [varchar](20) NULL,
[CheckNotes] [varchar](256) NULL,
[Email1] [varchar](80) NULL,
[bIsDuplicate] [bit] NULL,


Kindest Regards,

Just say No to Facebook!


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum