CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Chahk
Chahk

Reputation: 75

How to get previous value from a log

I have a situation where at some point in the past some records in a table were modified to have duplicated information.

Consider an example below:

create table #CustomerExample
(
   CustomerRecordId int,
   CustomerId int,
   CustomerName varchar(255),
   CurrentCustomerValue varchar(255)
);

create table #CustomerExampleLog
(
   LogId int,
   CustomerRecordId int,
   CustomerId int,
   LogCreateDate datetime,
   NewCustomerValue varchar(255)
);

insert #CustomerExample
values
(1, 100, 'Customer 1', 'Value X'),
(2, 100, 'Customer 1', 'Value X'),
(3, 200, 'Customer 2', 'Value Z'),
(4, 200, 'Customer 2', 'Value Z'),
(5, 200, 'Customer 2', 'Value Z');

insert #CustomerExampleLog
values
(1, 1, 100, '1/1/2014', 'Value B'),
(2, 1, 100, '2/1/2014', 'Value C'),
(3, 1, 100, '3/1/2014', 'Value B'),
(4, 1, 100, '4/1/2014', 'Value X'),
(5, 1, 100, '5/1/2014', 'Value X'),
(6, 1, 100, '6/1/2014', 'Value X'),
(7, 2, 100, '1/1/2014', 'Value D'),
(8, 2, 100, '2/1/2014', 'Value E'),
(9, 2, 100, '3/1/2014', 'Value F'),
(10, 2, 100, '4/1/2014', 'Value G'),
(11, 2, 100, '5/1/2014', 'Value X'),
(12, 2, 100, '6/1/2014', 'Value X'),
(13, 3, 200, '1/2/2014', 'Value A'),
(14, 3, 200, '1/3/2014', 'Value A'),
(15, 3, 200, '1/4/2014', 'Value B'),
(16, 3, 200, '1/5/2014', 'Value Z'),
(17, 4, 200, '1/2/2014', 'Value A'),
(18, 4, 200, '1/3/2014', 'Value A'),
(19, 4, 200, '1/4/2014', 'Value Z');

Originally "Customer 1" and "Customer 2" had different values in CustomerValue column for each record in [#CustomerExample] table. However, due to lack of a proper unique constraint, a bunch of "bad" UPDATE statements resulted in duplicated info. The updates were logged to [#CustomerExampleLog] table, which contains only the ID of the updated record, the update date, and the new value. My goal is to re-trace the log entries and revert one of the duplicates to it's "last known good" value before it became a dupe.

Ideally, I want to revert the CurrentCustomerValue for one of the dupes to a previous value. In the above example it would be the LogId=3 for CustomerRecordId=1, and LogId=15 for CustomerRecordId=3.

I am completely stumped.

Upvotes: 0

Views: 136

Answers (2)

Dbloch
Dbloch

Reputation: 2366

If you are looking to do it selectively (one record at a time), this would update the value.

UPDATE Customerexample
   SET Currentcustomervalue = a.Newcustomervalue
  FROM Customerexamplelog a
 WHERE Logid IN(SELECT MAX(Logid)
                  FROM Customerexamplelog L
                       INNER JOIN Customerexample C ON L.Customerrecordid = C.Customerrecordid
                                                   AND L.Newcustomervalue <> C.Currentcustomervalue
                 WHERE L.Customerrecordid = @custid);

Upvotes: 0

EricZ
EricZ

Reputation: 6205

Do you want something like this?

SELECT  *
, prev_value = (
    SELECT TOP 1 NewCustomerValue 
    FROM #CustomerExampleLog l 
    WHERE c.CustomerRecordId = l.CustomerRecordId 
    AND l.NewCustomerValue <> c.CurrentCustomerValue 
    ORDER BY LogCreateDate DESC
   )
FROM    #CustomerExample c

Upvotes: 1

Related Questions