February 24, 2012 at 5:40 am
The database I am working with has a SQL Server 2008 table that contains a few million records representing a change history log. Each record that is inserted into the table includes values for the modify date, modify user, name of the field that was changed, and the current value of the field.
ChangeHistory
(
modifydate datetime,
modifyuser varchar(32),
fieldname varchar(100),
fieldvalue varchar(500)
)
Is there an efficient way to query this table and return a recordset that includes the old and new field values in a single record? For example, if the table includes records that indicate the Location1 field was changed to Delaware on 2/1, Oklahoma on 2/5 and Arkansas on 2/15, I'd like a recordset that looks like...
[font="Courier New"]
MODIFYDATE FIELDNAME NEWVALUE OLDVALUE MODIFYUSER
---------- --------- -------- -------- ----------
02/01/2012 Location1 Delaware -------- User 1
02/05/2012 Location1 Oklahoma Delaware User 2
02/15/2012 Location1 Arkansas Oklahoma User 3
[/font]
February 24, 2012 at 5:54 am
;with baseData as (
select modifydate, fieldname, fieldvalue, modifyuser, seq = ROW_NUMBER() over (partition by fieldname order by modifydate)
from ChangeHistory
)
select T1.modifydate, T1.fieldname, OldValue = T1.fieldvalue, newValue = isnull(T2.fieldvalue, '------'), T1.modifyuser
from baseData as T1
left join baseData as T2 on T2.fieldname = T1.fieldname
and T2.seq = T1.seq - 1
order by T1.fieldname, T1.seq
February 24, 2012 at 7:47 am
Thanks!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply