Search for elements with a certain distance of time (informing only time)

Question

Detail sqlserver(2008) Good evening, I have the following problem. I tried to search all day and found nothing. -I need to search for elements in the same table that have the same date (day, time ...) and a distance of time of at most 40 seconds from one to the other.

Example:

ID --- data
1      09-11-2018 19:01:10
2      09-11-2018 19:01:30
3      09-11-2018 19:01:40
----------------------------------------
4      09-11-2018 19:02:00  <--exemplo de dado que nao deveria entrar

NOTE: I want the query to search the data, with this distance of time according to the table without informing given any other than the seconds.

I tried it as follows: but without success, in the result some data is repeated and others do not match the expected.

select *
from TABELA t join
     TABELA tnext
     on 
     datepart(DAY,  t.Data) = datepart(DAY, tnext.Data) and 
     datepart(HOUR,  t.Data) = datepart(HOUR, tnext.Data) and
     datepart(minute,  t.Data) = datepart(minute, tnext.Data) and
     datediff(second,  t.Data, tnext.Data) < 40 

Answer 1

You can use the function LAG () if you are working on the 2012+ version next to the function DATEDIFF()

CREATE TABLE T(
  ID INT,
  Data DATETIME
);

INSERT INTO T VALUES
(1, '09-11-2018 19:01:10'),
(2, '09-11-2018 19:01:30'),
(3, '09-11-2018 19:01:40'),
(4, '09-11-2018 19:02:20'); --Você tem dados errados aqui não é 40 segundos

WITH C AS
(
SELECT ID,
       Data,
       CASE WHEN LAG(Data, 1, 0) OVER (ORDER BY ID) = '01/01/1900 00:00:00' THEN
        NULL
        ELSE
        LAG(Data, 1, 0) OVER (ORDER BY ID)
       END PrevDate
FROM T
)
SELECT * 
FROM C
WHERE DATEDIFF(Second, PrevDate, Data) = 40;

Returns:

+----+---------------------+---------------------+
| ID |        Data         |      PrevDate       |
+----+---------------------+---------------------+
|  4 | 11/09/2018 19:02:20 | 11/09/2018 19:01:40 |
+----+---------------------+---------------------+

Answer 2

For each row of the table there may be a subset of lines that are in the 40 second range. A single row may participate in more than one subset.

-- código #1
SELECT T1.ID, T1.Data, T2.Data
  from tabela as T1
       inner join tabela as T2 on T2.Data > T1.Data
                                  and T2.Data < dateadd (second, +40, T1.Data)
  order by T1.ID, T2.Data;

In code #1 result all lines with the same ID value form a subset.

Another solution:

-- código #2
SELECT T1.ID, T1.Data, T2.Data
  from tabela as T1
       cross join tabela as T2
  where T2.Data > T1.Data
        and T2.Data < dateadd (second, +40, T1.Data)
  order by T1.ID, T2.Data;