difference between value and second value

Question

I have this kind table in SQL Server 2014:

event_type    | value      | time
  ------------+------------+--------------------
   2          | 5          | 2015-05-09 12:42:00
   4          | -42        | 2015-05-09 13:19:57
   2          | 2          | 2015-05-09 14:48:30
   2          | 7          | 2015-05-09 12:54:39
   3          | 16         | 2015-05-09 13:19:57
   3          | 20         | 2015-05-09 15:01:09

Need to query difference between latest and second latest value. Of course, only for event which is registered more than once.

Gordon Linoff · Answer 1 · 2017-10-22T20:41:12.380

1

Is this what you want?

select t.*, (value - prev_value) as diff
from (select t.*,
             lag(value) over (partition by event_type order by time) as prev_value
      from t
     ) t
where prev_value is not null;

EDIT:

lag() is usually more efficient, but you can also use apply:

select t.*, (t.value - prev.value) as diff
from t cross apply
     (select top 1 tprev.*
      from t tprev
      where tprev.event_type = t.event_type and tprev.time < t.time
      order by tprev.time desc
     ) tprev;

edited Oct 22 '17 at 20:41

answered Oct 22 '17 at 19:07

Gordon Linoff

1,242,037
58
646
786

What is `select @@version`? – Martin Smith Oct 22 '17 at 19:19
At my side is SQL Server 2014, but on the other side (production) where I don't have access SQL Server 2005. Sorry, but now I got info. – Darko Milic Oct 22 '17 at 19:20

score 1 · Answer 2 · answered Oct 22 '17 at 19:47

Here's a way to get the difference in the latest and second latest value using row_number

select event_type, sum(case when rn = 1 then value else -value end) from (
    select event_type, value, 
        row_number() over (partition by event_type order by time desc) rn
    from mytable
) t where rn <= 2
group by event_type
having count(*) = 2

If you want the absolute difference then you can use abs(sum(...))

difference between value and second value

2 Answers2