February 10, 2015 at 5:02 am
Hi there,
I have a table called [dbo].[co_audit_trail]
All records that get changed in our software gets written to this table.
CREATE TABLE [dbo].[co_audit_trail](
[seq_no] [bigint] IDENTITY(1,1) NOT NULL,
[create_complete] [tinyint] NULL,
[user_name] [varchar](60) NULL,
[db_event] [varchar](16) NULL,
[date_of_change] [datetime] NULL,
[time_of_change] [varchar](16) NULL,
[bi] [varchar](8000) NULL,
[ai] [varchar](8000) NULL,
[table_recid] [int] NULL,
[modified_table] [varchar](60) NULL,
[archive_date] [datetime] NULL,
[archive_time] [varchar](16) NULL,
[modified_db] [varchar](60) NULL
) ON [PRIMARY]
GO
Within this table you have the bi and ai (which I presume stands for Before Image and After image.
An example of the data (sorry I would use the self loading technique but it won't work for those two fields?)
BI -
01ª9999999999ªHSGªªRENTONLYªDª ª ª27/07/1990ªª0.00ª0.00ª0ª0ªªªªªª ª*ªªª ªSOLDªª0ªª0.00ª0ª0.00ª0ªª ª ª ªJD1ª06/06/2005ª15:22:09ªco-rkª07/01/2015ª12:15:32ª ªEASTª ªWª0ªª0ª ª ª ª ªªª ª ª ª ª ªDEMLª0.00ªªªªª0ªMEDWAYªªALLª0.00ª0.00ªª0.00ª0ª100ª0002/00ª06/0000ª ª ªª ª ª0ª0ª0ª0ª ª0.00ª ª ª ª ª ª ª ª ª ª ª ª ª ª ªª ª ª ªª ªEnglandª0.00ª0.00ª0ªª0ª0ª0ª ª0ª0ª0.00ª0.00ª0.00ªNon-Decentª ª ª0ª ª ª ª ª ª ª ª ª ª ª ª ªª0ª0ª0ª ª ª ª0.00ª0ª0ª0ª0ª0ª0ª0ª ª ª0ª0ª ª0ª ª ªª0ª ª ª ª ª ª ª ª ª ª ª ª ª ª0ª ª ª ª ª ª ª0ª ª ª0ª0ª0ª ª ª ª ª0ª ªªª13041ª13041ª0ª
AI -
ai
01ª9999999999ªHSGªªRENTONLYªDª ª ª27/07/1990ªª0.00ª0.00ª0ª0ªªªªªª ª*ªªª ªSOLDªª0ªª0.00ª0ª0.00ª0ªª ª ª ªJD1ª06/06/2005ª15:22:09ªco-rkª07/01/2015ª12:15:32ª ªWESTª ªWª0ªª0ª ª ª ª ªªª ª ª ª ª ªDEMLª0.00ªªªªª0ªMEDWAYª*ªALLª0.00ª0.00ªª0.00ª0ª100ª0002/00ª06/0000ª ª ªª ª ª0ª0ª0ª0ª ª0.00ª ª ª ª ª ª ª ª ª ª ª ª ª ª ªª ª ª ªª ªEnglandª0.00ª0.00ª0ªª0ª0ª0ª ª0ª0ª0.00ª0.00ª0.00ªNon-Decentª ª ª0ª ª ª ª ª ª ª ª ª ª ª ª ªª0ª0ª0ª ª ª ª0.00ª0ª0ª0ª0ª0ª0ª0ª ª ª0ª0ª ª0ª ª ªª0ª ª ª ª ª ª ª ª ª ª ª ª ª ª0ª ª ª ª ª ª ª0ª ª ª0ª0ª0ª ª ª ª ª0ª ªªª13041ª13041ª0ª
1. It looks like a 'ª' delimiters between fields. I need to pull out the second column in the above example it is - 9999999999. This is the place reference, so I need that in a separate field.
2. I need a way to compare the two fields and report back the change. So in the above example shows EAST. The After image shows WEST - I need the before image of EAST in one column and the WEST in another column.
February 10, 2015 at 6:13 am
neat problem.
find the DelimitedSplit8k function here, and it's a peice of cake.
the set up for testing:
CREATE TABLE [#co_audit_trail](
[seq_no] [bigint] IDENTITY(1,1) NOT NULL,
[create_complete] [tinyint] NULL,
[user_name] [varchar](60) NULL,
[db_event] [varchar](16) NULL,
[date_of_change] [datetime] NULL,
[time_of_change] [varchar](16) NULL,
[bi] [varchar](8000) NULL,
[ai] [varchar](8000) NULL,
[table_recid] [int] NULL,
[modified_table] [varchar](60) NULL,
[archive_date] [datetime] NULL,
[archive_time] [varchar](16) NULL,
[modified_db] [varchar](60) NULL
)
INSERT INTO [#co_audit_trail](bi,ai)
SELECT '01ª9999999999ªHSGªªRENTONLYªDª ª ª27/07/1990ªª0.00ª0.00ª0ª0ªªªªªª ª*ªªª ªSOLDªª0ªª0.00ª0ª0.00ª0ªª ª ª ªJD1ª06/06/2005ª15:22:09ªco-rkª07/01/2015ª12:15:32ª ªEASTª ªWª0ªª0ª ª ª ª ªªª ª ª ª ª ªDEMLª0.00ªªªªª0ªMEDWAYªªALLª0.00ª0.00ªª0.00ª0ª100ª0002/00ª06/0000ª ª ªª ª ª0ª0ª0ª0ª ª0.00ª ª ª ª ª ª ª ª ª ª ª ª ª ª ªª ª ª ªª ªEnglandª0.00ª0.00ª0ªª0ª0ª0ª ª0ª0ª0.00ª0.00ª0.00ªNon-Decentª ª ª0ª ª ª ª ª ª ª ª ª ª ª ª ªª0ª0ª0ª ª ª ª0.00ª0ª0ª0ª0ª0ª0ª0ª ª ª0ª0ª ª0ª ª ªª0ª ª ª ª ª ª ª ª ª ª ª ª ª ª0ª ª ª ª ª ª ª0ª ª ª0ª0ª0ª ª ª ª ª0ª ªªª13041ª13041ª0ª',
'01ª9999999999ªHSGªªRENTONLYªDª ª ª27/07/1990ªª0.00ª0.00ª0ª0ªªªªªª ª*ªªª ªSOLDªª0ªª0.00ª0ª0.00ª0ªª ª ª ªJD1ª06/06/2005ª15:22:09ªco-rkª07/01/2015ª12:15:32ª ªWESTª ªWª0ªª0ª ª ª ª ªªª ª ª ª ª ªDEMLª0.00ªªªªª0ªMEDWAYª*ªALLª0.00ª0.00ªª0.00ª0ª100ª0002/00ª06/0000ª ª ªª ª ª0ª0ª0ª0ª ª0.00ª ª ª ª ª ª ª ª ª ª ª ª ª ª ªª ª ª ªª ªEnglandª0.00ª0.00ª0ªª0ª0ª0ª ª0ª0ª0.00ª0.00ª0.00ªNon-Decentª ª ª0ª ª ª ª ª ª ª ª ª ª ª ª ªª0ª0ª0ª ª ª ª0.00ª0ª0ª0ª0ª0ª0ª0ª ª ª0ª0ª ª0ª ª ªª0ª ª ª ª ª ª ª ª ª ª ª ª ª ª0ª ª ª ª ª ª ª0ª ª ª0ª0ª0ª ª ª ª ª0ª ªªª13041ª13041ª0ª'
--here's where i found it was itemNumber 44:
select * from [#co_audit_trail]
cross apply master.dbo.DelimitedSplit8K(ai,'ª')
and i think this is the results you wanted:
select ref1.*,ref2.*,f1.*,f2.*,t1.* from [#co_audit_trail] t1
cross apply (select Item FROM master.dbo.DelimitedSplit8K(bi,'ª') WHERE ItemNumber = 2) ref1
cross apply (select Item FROM master.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 2) ref2
cross apply (select Item FROM master.dbo.DelimitedSplit8K(bi,'ª') WHERE ItemNumber = 44) f1
cross apply (select Item FROM master.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 44) f2
Lowell
February 10, 2015 at 6:17 am
if you want to find the changes, no matter what they were, and not on a specific field use this:
/*--results
ItemNumber Item ItemNumber Item seq_no
44 EAST 44 WEST 1
69 69 * 1
*/
select f1.*,f2.*,t1.* from [#co_audit_trail] t1
cross apply master.dbo.DelimitedSplit8K(bi,'ª') f1
cross apply master.dbo.DelimitedSplit8K(ai,'ª') f2
WHERE f1.ItemNumber = f2.ItemNumber
AND f1.Item <> f2.Item
Lowell
February 10, 2015 at 6:35 am
You beauty - this will work a treat - off to play now 🙂
February 10, 2015 at 8:29 am
Worked a treat -
Ended up with the following code -
selectp.Item AS 'PlaceReferance'
,PL.address##1 AS 'AddressLine1'
,PL.address##2 AS 'AddressLine2'
,CASE WHEN bi.ItemNumber = '5' THEN 'LocationType'
WHEN bi.ItemNumber = '44' THEN 'ManagementArea'
WHEN bi.ItemNumber = '69' THEN 'Scheme'
ELSE '' END AS 'ChangeType'
,au.date_of_change AS 'ChangeDateTime'
,au.user_name AS 'User'
,bi.Item 'Before'
,ai.Item 'After'
from [co_audit_trail] au
/*Uses Stored Procedure DelimitedSplit8K to work out parts of field*/
cross apply mhsInsight.dbo.DelimitedSplit8K(bi,'ª') bi
cross apply mhsInsight.dbo.DelimitedSplit8K(ai,'ª') ai
cross apply (select Item FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 2) p
INNER JOIN
[dbo].[co_place] as pl
ONP.Item = PL.place_ref
where au.modified_table = 'ih_location'
and au.user_name <> 'ibssrv'
and bi.ItemNumber = ai.ItemNumber
and bi.Item <> ai.Item
/*Only show changes to Location Type, ManagementArea and Scheme*/
and bi.ItemNumber IN ('5','44','69')
Order By au.date_of_change
February 13, 2015 at 9:49 am
I had to work with an audit log like that and it was a nightmare!
What happens if the text field contains your delimiter value
what happens if the structure of the table changes (e.g. someone inserts a record into the middle of the column list) - All the old audit records no longer match the pattern of the current table structure.
You could recommend that they should be using the CDC (Change Data Capture) built into SQL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply