How to sum the first 2 occurrences of a column using a key as a reference in SQLServer

0

Hello, I have the following table:

create table events (
    type int not null,
    value int not null,
    time datetime
    unique(type, time)
);

Where in the same I have the following records:

------------------------------------------
| type | value | time                    |
------------------------------------------
|2     | 5     | 2015-05-09 12:42:00.000 | 
|4     | -42   | 2015-05-09 13:19:57.000 |
|2     | 2     | 2015-05-09 14:48:30.000 |
|2     | 7     | 2015-05-09 12:54:39.000 |
|3     | 16    | 2015-05-09 13:19:57.000 |
|3     | 20    | 2015-05-09 15:01:09.000 |
------------------------------------------

I need to, for every event that has more than one occurrence, I take the last two logged events and return the difference between their values.

For example, for the type 2 event, I would have to return the 1st and 4th line, as well as to calculate the difference between the values (5 - 7 = -2) and return in the table.

To list recurring events, I'm using SQL below:

select
    type,
    (select top 1 value 
     from events 
     where type = e.type 
     order by time desc
    ) - (select value 
         from events 
         where type = e.type 
         order by time desc 
         offset 1 rows fetch next 1 row only) as value
FROM events e
group by type
having count(type) > 1

The problem is that I have no idea how I can return only the first 2 occurrences of each event and make the calculation between the first and second values.

To solve my problem, I was forced to do 2 queries as a result for a value in the table. The problem is that this is not optimized because they are 2 SELECT's the most and that (it is not possible that it does not exist) must have some better way to do ...

Is there any way to optimize this? Do not need to do the 2 SQL to add the last 2%% of the 2 occurrences of the table?

    
asked by anonymous 29.11.2018 / 20:13

0 answers