Displaying the change history from the history table
I have a History table which is created by Insert and Update triggers. The History line contains the line specified in the Insert / Update section.
What I'm being asked to do is show the changes for each user over time. So below is what I have in the form of my History table and then I created dummy expected results.
DECLARE @MyTable TABLE
(
id INT NOT NULL IDENTITY(1,1),
userId INT NOT NULL,
locationId INT NOT NULL,
roleId INT NOT NULL,
lastUpdateUserId INT NOT NULL,
lastUpdateDate DATETIME NOT NULL
)
INSERT INTO @MyTable
(userId, locationId, roleId, lastUpdateUserId, lastUpdateDate)
SELECT 1, 1000, 1, 7, GETDATE()+1 UNION
SELECT 2, 1100, 5, 9, GETDATE()+2 UNION
SELECT 2, 1110, 5, 6, GETDATE()+3 UNION
SELECT 1, 1100, 3, 6, GETDATE()+4 UNION
SELECT 4, 1500, 5, 8, GETDATE()+5 UNION
SELECT 7, 1000, 8, 9, GETDATE()+6 UNION
SELECT 7, 1100, 9, 9, GETDATE()+7 UNION
SELECT 1, 1000, 3, 7, GETDATE()+8 UNION
SELECT 9, 1100, 5, 2, GETDATE()+9 UNION
SELECT 9, 1100, 6, 5, GETDATE()+10
SELECT * FROM @MyTable ORDER BY Id
DECLARE @ExpectedResult TABLE
(
ChangeType CHAR(1), -- I=Insert, U=Update
UserId INT,
ChangeDate DATETIME,
ChangedByUser INT,
FieldName VARCHAR(20),
OldValue INT,
NewValue INT
)
INSERT INTO @ExpectedResult
(ChangeType, UserId, ChangeDate, ChangedByUser, FieldName, OldValue, NewValue)
SELECT 'I', 1, '2015-APR-30 09:56:28', 7, 'locationId', NULL, 1000 UNION -- Row1
SELECT 'I', 1, '2015-APR-30 09:56:28', 7, 'roleId', NULL, 1 UNION -- Row1
SELECT 'U', 1, '2015-APR-07 10:27:42', 7, 'roleId', 1, 3 UNION -- Row 2
SELECT 'U', 1, '2015-MAY-03 10:27:42', 6, 'locationId', 1000, 1100 UNION -- Row 3
SELECT 'I', 2, '2015-MAY-01 10:27:42', 9, 'roleId', NULL, 5 UNION -- Row5
SELECT 'I', 2, '2015-MAY-01 10:27:42', 9, 'locationId', NULL, 1100 -- Row5
SELECT * FROM @ExpectedResult
@MyTable has the data as it is at the moment. I am trying to convert this to @ExpectedResults. We are reporting changes in roleId and locationId. Each time you change, you must have a separate row for each column. Thus, when inserting, we have two rows (since we are tracking changes in both fields). When one column is updated, it should be represented as a single "U" row. If both fields are updated in the same UPDATE statement, this will result in two update lines in @Expected.
I started with a Cursor, but hoped there would be a more efficient way to achieve this.
source to share
To get the roleID and locationID on separate lines, you can use a simple UNION ALL.
And to combine the old and new values, use the ROW_NUMBER () window function, for example:
;with t as(
select *,
ROW_NUMBER() OVER(partition by userid Order BY lastUpdateDate) rn
from @MyTable
),
a as (
select userId, 'locationId' as fieldname,
locationId as value, lastUpdateUserId, lastUpdateDate, rn
from t
UNION ALL
select userId, 'roleId' as fieldname,
roleId as value, lastUpdateUserId, lastUpdateDate, rn
from t
)
select CASE WHEN a2.userId IS NULL THEN 'I' ELSE 'U' END as ChangeType,
a1.userId, a1.lastUpdateDate, a1.lastUpdateUserId, a1.fieldname, a1.value as newValue, a2.value as oldvalue
FROM a a1 LEFT JOIN a a2
ON a1.userId = a2.userId and a1.fieldname = a2.fieldname
AND a1.rn = a2.rn+1
order by 2,3,5
The alias a1
in the above query contains "new values", a2
contains "old values". When using real data you will also need to strip the field name (and possibly the table name) as well as join them
Result:
ChangeType userId lastUpdateDate lastUpdateUserId fieldname newValue oldvalue
---------- ----------- ----------------------- ---------------- ---------- ----------- -----------
I 1 2015-04-30 12:20:59.183 7 locationId 1000 NULL
I 1 2015-04-30 12:20:59.183 7 roleId 1 NULL
U 1 2015-05-03 12:20:59.183 6 locationId 1100 1000
U 1 2015-05-03 12:20:59.183 6 roleId 3 1
U 1 2015-05-07 12:20:59.183 7 locationId 1000 1100
U 1 2015-05-07 12:20:59.183 7 roleId 3 3
I 2 2015-05-01 12:20:59.183 9 locationId 1100 NULL
I 2 2015-05-01 12:20:59.183 9 roleId 5 NULL
U 2 2015-05-02 12:20:59.183 6 locationId 1110 1100
U 2 2015-05-02 12:20:59.183 6 roleId 5 5
I 4 2015-05-04 12:20:59.183 8 locationId 1500 NULL
I 4 2015-05-04 12:20:59.183 8 roleId 5 NULL
I 7 2015-05-05 12:20:59.183 9 locationId 1000 NULL
I 7 2015-05-05 12:20:59.183 9 roleId 8 NULL
U 7 2015-05-06 12:20:59.183 9 locationId 1100 1000
U 7 2015-05-06 12:20:59.183 9 roleId 9 8
I 9 2015-05-08 12:20:59.183 2 locationId 1100 NULL
I 9 2015-05-08 12:20:59.183 2 roleId 5 NULL
U 9 2015-05-09 12:20:59.183 5 locationId 1100 1100
U 9 2015-05-09 12:20:59.183 5 roleId 6 5
(20 row(s) affected)
source to share