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

Question

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

Navigation

#1 by (0 votes)
#2 by (0 votes)

-1

Detail sqlserver (2008) Good night, I have the following problem. I tried searching all day and found nothing. -I need to find elements in a same table that have the same date (day, time ...) and a time distance 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

>

I tried the following way: but without success, in the result some data is repeated and others do not match what was 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

sql sql-server sql-server-2008

asked by anonymous 09.11.2018 / 22:08

2 answers

Transitions on a page How to customize CSS (tr: hover) in a table with thead?

score 0 · Answer 1

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

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 |
+----+---------------------+---------------------+

score 0 · Answer 2

For each row of the table there may be a subset of rows that are in the range of 40 seconds. The same row can join 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 the result of code # 1 all rows 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;