Build change history from table with historical row values

Question

I have table that is storing historical values for data in other table:

ObjectId | Value | UpdatedAt 1 | A | 2020-07-15 1 | B | 2020-07-16 1 | C | 2020-07-17 2 | A | 2020-07-15 2 | B | 2020-07-16

Now I need to generate from such table "change log" which will show what was old value, new value and when update occur:

ObjectId | OldValue | NewValue | UpdatedAt 1 | A | B | 2020-07-16 1 | B | C | 2020-07-17 2 | A | B | 2020-07-16

Unfortunately, I can't change structure of the existing table and can't put old value there, I need a query to extract this.

updated example with non-number values, I need varchars actually, so min/max isn't an option — deha
– deha, Commented Jul 15, 2020 at 5:35

zealous · Accepted Answer · 2020-07-15 05:35:09Z

You can use window function lead() to do partition on ObjectId. Here is the demo.

select ObjectId, Value, NewValue, UpdatedAt from ( select ObjectId, Value, lead(value) over (partition by ObjectId order by UpdatedAt) as NewValue, lead(UpdatedAt) over (partition by ObjectId order by UpdatedAt) as UpdatedAt from Table1 ) subq where NewValue is not null order by ObjectId

output:

| ObjectId Value NewValue UpdatedAt | *-------------------------------------* | 1 1 2 2020-07-16 | | 1 2 3 2020-07-17 | | 2 1 2 2020-07-16 |

Venkataraman R · Accepted Answer · 2020-07-15 05:37:13Z

You can use windowing functions to achieve the result.

DECLARE @historyTable table(ObjectId int, Value int, UpdatedAt date) insert into @historyTable values (1 , 1 ,'2020-07-15'), (1 , 2 ,'2020-07-16'), (1 , 3 ,'2020-07-17'), (2 , 1 ,'2020-07-15'), (2 , 2 ,'2020-07-16'); SELECT * from ( SELECT objectid, value as oldvalue, lead(value,1) over (partition by objectid order by updatedat) as newvalue, lead(UpdatedAt,1) over (partition by objectid order by updatedat) as updatedat FROM @historyTable ) as t where t.updatedat is not null

 +----------+----------+----------+------------+ | objectid | oldvalue | newvalue | updatedat | +----------+----------+----------+------------+ | 1 | 1 | 2 | 2020-07-16 | | 1 | 2 | 3 | 2020-07-17 | | 2 | 1 | 2 | 2020-07-16 | +----------+----------+----------+------------+

Wei Lin · Accepted Answer · 2020-07-15 05:36:38Z

with cte as ( select row_number() over (partition by objectid order by UpdatedAt ) rnk,[ObjectId], [Value], [UpdatedAt] from T ) select t1.ObjectId,t2.Value as OldValue ,t1.Value as NewValue ,t1.UpdatedAt from ( select * from cte where rnk <> 1 ) t1 left join cte t2 on t1.objectid =t2.objectid and t1.rnk -1 = t2.rnk

table script :

CREATE TABLE T ([ObjectId] int, [Value] int, [UpdatedAt] datetime) ; INSERT INTO T ([ObjectId], [Value], [UpdatedAt]) VALUES (1, 1, '2020-07-15 00:00:00'), (1, 2, '2020-07-16 00:00:00'), (1, 3, '2020-07-17 00:00:00'), (2, 1, '2020-07-15 00:00:00'), (2, 2, '2020-07-16 00:00:00') ;

demo link

Hesam Akbari · Accepted Answer · 2020-07-15 06:18:31Z

 with changeLogTemp as ( select ROW_NUMBER() over (order by ObjectId,UpdatedAt ) row_number ,* from changeLog ) select l.ObjectId, l.Value OldValue, r.Value NewValue, r.UpdatedAt from changeLogTemp l left join changeLogTemp r on l.ObjectId =r.ObjectId and l.row_number =r.row_number-1 where r.UpdatedAt is not null

You can also use Widowfunction (Lead()) to solve this situation

But the proposed solution is very simple And at the same time, it is very useful for similar operations(computational and comparative) between rows and two or more columns

Collectives™ on Stack Overflow

Build change history from table with historical row values

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Related