Is there a better way to query for keyword update history than parsing the from/to values out of the transactionxlog.tmessage field?
I'm using the below to get at this information but am wondering if there is a more efficient way to get at keyword update history.
select id.itemnum as 'Doc Handle',
id.datestored as 'Import Date',
rtrim(ktt.keytype) as 'Changed Field',
rtrim(ua.username) as 'Changed by User Login',
rtrim(ua.realname) as 'Changed by User Name',
CASE txl.action
WHEN 1034 THEN 'Deleted'
WHEN 1035 THEN 'Added'
END AS "Action",
txl.tmessage,
txl.logdate as 'Date Changed',
SUBSTRING(txl.tmessage,
charindex('''',txl.tmessage) + LEN(''''),
((charindex('''',txl.tmessage,charindex('''',txl.tmessage) + LEN(''''))) - (charindex('''',txl.tmessage) + LEN('''')))) as 'Value'
Left join hsi.transactionxlog txl on id.itemnum = txl.num
left join hsi.useraccount ua on txl.usernum = ua.usernum
left join hsi.keytypetable ktt on txl.extrainfo1 = ktt.keytypenum
where id.datestored between @{daterange}
And txl.action in (1034,1035)
and txl.extrainfo1 in (208,121,115,114,198,119,118,117,122,120,116,125) --KW Nums
ORDER BY id.itemnum
Thank you,
Carissa
