CODEWITHSUNDEEP
sqlsql-servert-sql
Smooyk
Smooyk

Reputation: 409

Collect all rows that have a change in some column during date interval

I have History table that contains a history log for changes against the target object and I want to select rows that have Value changed during some time period.

Example data:

TargetId  Value  CreatedUtcDateTime 
1         1      2018-03-09
1         1      2018-04-09
1         2      2018-04-10
2         3      2018-05-10
2         4      2018-06-10
2         5      2018-07-10
3         4      2017-08-10
3         1      2018-09-12
3         2      2018-10-13

Expected result for interval from '2018-01-01' to '2019-02-01' is:

TargetId OldValue NewValue CreatedUtcDateTime 
1        1        2        2018-04-10
2        3        4        2018-06-10
2        4        5        2018-07-10
3        4        1        2018-09-12
3        1        2        2018-10-13

So far I came up with the solution that is based on the windows functions:

select c1.TargetId as Coil, 
       c2.Value as OldValue, 
       c1.Value as NewValue, 
       c1.CreatedUtcDateTime as ChangeDate
from (select ROW_NUMBER() over (partition by TargetId order by CreatedUtcDateTime) as rowNum, 
     TargetId, 
     Value, 
     CreatedUtcDateTime  from History) c1
join ( select ROW_NUMBER() over (partition by TargetId order by CreatedUtcDateTime) as rowNum, 
              TargetId, 
              Value, 
              CreatedUtcDateTime 
         from History) c2
  on c1.rowNum = c2.rowNum + 1 
 and c1.TargetId = c2.TargetId 
 and c1.Value != c2.Value 
 and c1.CreatedUtcDateTime > c2.CreatedUtcDateTime
 where c1.CreatedUtcDateTime > '2018-01-01' 
   and c1.CreatedUtcDateTime < '2019-02-01'

But this produces two full table scans and I want to avoid this. Is there more efficient way to achieve this?

Upvotes: 1

Views: 961

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use lag():

select h.*
from (select h.*, lag(value) over (partition by targetId order by CreatedUtcDateTime) as old_value
      from history h
     ) h
where CreatedUtcDateTime >= '2018-01-01' and
      CreatedUtcDateTime < '2019-02-01' and
      old_value <> value;  -- I can't tell if this is necessary

The important point is that the time filtering goes in the outer query not the inner one. Otherwise, you'll miss the first change during the period.

Upvotes: 3

Related Questions