CODEWITHSUNDEEP
sqlsql-servert-sql
deha
deha

Reputation: 815

Build change history from table with historical row values

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.

Upvotes: 0

Views: 205

Answers (4)

Hesam Akbari
Hesam Akbari

Reputation: 1141

    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

Upvotes: 0

Venkataraman R
Venkataraman R

Reputation: 12959

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 |
+----------+----------+----------+------------+

Upvotes: 1

Wei Lin
Wei Lin

Reputation: 3811

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

image

Upvotes: 0

zealous
zealous

Reputation: 7503

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 |

Upvotes: 2

Related Questions