Skip to content
Advertisement

SQL Return last record where column value changed and amount of change

In MS SQL 2012, I have a table like such:

    Key    |    Entity    |    Value  |    DateTime
    -----------------------------------------------------------
    10           A              800        1/1/2019 12:00:00 AM
    08           A              800        1/1/2018 12:00:00 AM
    06           A              700        1/1/2017 12:00:00 AM
    04           A              725        1/1/2016 12:00:00 AM
    09           B              550        1/1/2019 12:00:00 AM
    07           B              400        1/1/2018 12:00:00 AM
    05           B              400        1/1/2017 12:00:00 AM
    03           B              375        1/1/2016 12:00:00 AM

I would like to return the date of change and the amount of change — from the last record where the value changed from the record before it.

Ideally, this:

    Entity    |    ValueChange    |    DateofChange
    ------------------------------------------------
    A              100                 1/1/2018
    B              150                 1/1/2019

Entity A last changed on 1/1/2018 by 100, Entity B last changed on 1/1/2019 by 150

I’m thinking it’s going to be some combination of Lag and maybe this discussion: SQL: selecting rows where column value changed from previous row

But I’m very much a SQL beginner and have no formal training, and I appreciate any detail and extra explanations you can provide.

Thank you!

Advertisement

Answer

First with a CTE which returns all the changes and then with NOT EXISTS that returns the last change for each Entity:
(Edited with the actual table and column names)

with cte as (
  select t.hUnit UnitID, (t.cRent - tt.cRent) cRentChange, t.dtDate DateofChange
  from unit_history t inner join unit_history tt
  on 
    tt.hUnit = t.hUnit 
    and 
    tt.dtDate = (
      select max(dtDate) 
      from unit_history 
      where hUnit = t.hUnit and dtDate < t.dtDate
    )
  where t.cRent <> tt.cRent
)

select c.* from cte c
where not exists (
  select 1 from cte
  where UnitID = c.UnitID and DateofChange > c.DateofChange
)

See the demo.
Results:

 Entity | ValueChange | DateofChange       
> :----- | ----------: | :------------------
> A      |         100 | 01/01/2018 00:00:00
> B      |         150 | 01/01/2019 00:00:00
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement