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

Question

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 that, for every event that there is more than one occurrence, I take the last two recorded events and return the difference between their values.

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

To list the recurring events, I am 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 values of the first and second.

To solve my problem, I was required to make 2 queries as a result for a value in the table. The problem that this is not optimized because they are 2 SELECT’s more and that (it is not possible that it does not exist) should 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 value of the 2 occurrences of the table?

Answer 1

Good morning.

The solution to your case would be to use a common-table-Expression.

In the given example there is an inconsistency in writing, would you like the most recent or the oldest? Given the answer there is still a need to execute a change in the query, only in order by of row_number.

WITH summary AS (
    SELECT p.type, 
           p.value, 
           p.time,
           ROW_NUMBER() OVER(PARTITION BY p.type 
                                 ORDER BY p.time DESC) AS rk
      FROM eventos p)
 select t.type, (select value from summary p where p.rk = 1 and p.type = t.type) - 
             (select value from summary p where p.rk = 2 and p.type = t.type)
   from summary t
  where t.rk < 3
    and exists (
         select e.type, COUNT(1)
           from eventos e
          where e.type = t.type
          group by e.type
         having COUNT(1) > 1)
  group by t.type

Follow SQL Server documentation: https://docs.microsoft.com/pt-br/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017