A Change to the audit script

--Audit Script--

select CONCAT([Forename],' ' ,[Surname]) AS 'StaffName', date_of_change as 'changedate', [Job Title Before], [Job Title After]

from co_audit_trail2

cross apply (select Item AS 'Job Title Before' FROM mhsInsight.dbo.DelimitedSplit8K(bi,'ª') WHERE ItemNumber = 29) o --Job TitleBEFORE--

cross apply (select Item AS 'Job Title After' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 29) o1 --Job TitleAFTER--

cross apply (select Item AS 'Forename' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 17) o2 --Forename--

cross apply (select Item AS 'Surname' FROM mhsInsight.dbo.DelimitedSplit8K(ai,'ª') WHERE ItemNumber = 5) o3 --Surname--

where [Surname] = 'Keast'

So I made three changes to the officer of Ryan Keast

2015-02-27 16:00:00.000

2015-02-27 16:02:00.000

2015-03-02 09:35:00.000

Before the first change I logged a CRM entry

at 2015-02-27 15:56:17.000

After my first audit change I logged another at

2015-02-27 16:01:06.000

Then a final audit change at

2015-02-27 16:02:31.000

As I need to report on what the job title was at the time of the crm entry - all I have is this horrendous audit table.

So for the CRM entry logged on 2015-02-27 15:56:17.000 the job title was ICT Systems Analyst

The second entry at 2015-02-27 16:01:06.000 was ICT Systems Analyst 1

The Final on at 2015-02-27 16:02:31.000 was ICT Systems Analyst 2

Does that make any sense?