Follow-up to Track Field/Column Level Changes.

This just a follow-up post to: Is This Too Much? An Attempt To Track Field/Column Level Changes.


Here's an example of how to make use of your new Tracking.

Run these sample updates
--// Sample updates
UPDATE
Customer
SET
FirstName = 'Bob'
, LastName = 'Mihada'
, WorkPhone = '808-808-808'
, Email = 'fake@uout.com'
, STATE = 'HI'
WHERE
CustomerId = 566

UPDATE
Customer
SET
LastName = 'Beans'
WHERE
CustomerId = 566

UPDATE
Customer
SET
MiddleName = 'K'
WHERE
CustomerId = 566

UPDATE
Customer
SET
MiddleName = ''
WHERE
CustomerId = 566

UPDATE
Customer
SET
FirstName = 'Bob'
, LastName = 'Mihada'
, WorkPhone = '808-808-808'
, Email = 'spambot@die32123.we3w.34.com'
, STATE = 'HI'
WHERE
CustomerId = 566

Then run this to view changes(Note this limited to LastName, FirstName, MiddleName)
--// Retrieve history
DECLARE @CustomerID INT
SELECT
@CustomerID = 566;
WITH CTE
AS (
SELECT
A.CustomerID
, ROW_NUMBER() OVER (PARTITION BY A.CustomerFieldId ORDER BY CustomerFieldAuditDateRecorded) Interval
, CASE WHEN A.CustomerFieldId = 2
THEN A.CustomerFieldAuditValue.value('(/)[1]',
'nvarchar(100)')
END LastName
, CASE WHEN A.CustomerFieldId = 3
THEN A.CustomerFieldAuditValue.value('(/)[1]',
'nvarchar(100)')
END FirstName
, CASE WHEN A.CustomerFieldId = 4
THEN A.CustomerFieldAuditValue.value('(/)[1]',
'nvarchar(100)')
END MiddleName
, A.CustomerFieldAuditDateRecorded
FROM
CustomerFieldAudit A (NOLOCK)
WHERE
A.CustomerID = @CustomerID
)
SELECT
Interval
, CASE WHEN MAX(C.LastName) IS NOT NULL THEN MAX(C.LastName)
ELSE P.LastName
END LastName
, CASE WHEN MAX(C.FirstName) IS NOT NULL THEN MAX(C.FirstName)
ELSE P.FirstName
END FirstName
, CASE WHEN MAX(C.MiddleName) IS NOT NULL THEN MAX(C.MiddleName)
ELSE P.MiddleName
END MiddleName
, MAX(CustomerFieldAuditDateRecorded) CustomerFieldAuditDateRecorded
FROM
CTE C
JOIN Customer P ON P.CustomerID = C.CustomerID
WHERE
P.CustomerID = @CustomerID
GROUP BY
C.INTERVAL
, P.LastName
, P.FirstName
, P.MiddleName

Should end up with something like this:
IntervalLastNameFirstNameMiddleNameCustomerFieldAuditDateRecorded
1GreenFrankE2011-02-02 21:33:40.743
2MihadaBobK2011-02-02 21:33:40.750
3BeansBob 2011-02-02 21:33:40.750

~Enjoy.

Comments :

0 comments to “Follow-up to Track Field/Column Level Changes.”