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

• YSLGuru

  SSC-Insane

  Points: 21632

  More actions

  April 22, 2014 at 6:16 pm

  #292644

  ************************************************************************************************************************************

  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

  Thanks

  CREATE TABLE [dbo].[PERSON_AUDIT](

  [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,

  CONSTRAINT [PK_PERSON_AUDIT] PRIMARY KEY CLUSTERED ([hID] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]

  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

  GO

  Kindest Regards,

  Just say No to Facebook!