Search Dates with Difference Defined in SQL Server

Asked

Viewed 220 times

0

How to perform a query in MICROSOFT SQL SERVER that before a defined number of days returns only the dates that meet this existing difference. for example

In a table have the following registered dates:

02/01/2018
06/01/2018
08/01/2018
15/01/2018
21/01/2018
26/01/2018
28/01/2018

And the period is defined as 4 days it shows only the dates that have the difference between 4 days.

Expected result :

02/01/2018
06/01/2018
08/01/2018
26/01/2018
28/01/2018
sql sql-server microsoft

  • @Claytofelippe What prompts you to return only those lines where the difference in dates between the line date and the next (or previous) line date is in the reported interval? // What is the version of SQL Server?

  • if the question has been answered, please mark the answer as correct.

2 answers

1


By the result example it is deduced that the query must return all rows where the date difference between the current line and the previous or later line is within the range.

Here is the suggestion that meets this requirement:

-- código #1
declare @Intervalo int;
set @Intervalo= 4;

with tabela_S as (
SELECT DataX,
       seq= row_number() over (order by DataX)
  from tbDatas
)
SELECT T1.DataX 
  from tabela_S as T1
       left join tabela_S as T2 
                 on T2.seq = T1.seq -1
       left join tabela_S as T3 
                 on T3.seq = T1.seq +1
  where datediff(day, T2.DataX, T1.DataX) <= @Intervalo
        or datediff(day, T1.DataX, T3.DataX) <= @Intervalo;

Substitute DataX by the name of the column containing the date and tbDatas by table name.

Here’s the code that generated the mass of data for testing:

-- código #2
CREATE TABLE tbDatas (DataX date);

INSERT into tbDatas values
  (convert(date, '02/01/2018', 103)),
  (convert(date, '06/01/2018', 103)),
  (convert(date, '08/01/2018', 103)),
  (convert(date, '15/01/2018', 103)),
  (convert(date, '21/01/2018', 103)),
  (convert(date, '26/01/2018', 103)),
  (convert(date, '28/01/2018', 103));

-1

The best result I can see is this::

DECLARE @IntervaloEmDias INT
SELECT  @IntervaloEmDias = 4

SELECT 
    TB1.DIA
FROM 
    NOME_TABELA TB1
INNER JOIN
    NOME_TABELA TB2 ON TB1.DIA <> TB2.DIA 
WHERE 
    ABS(DATEDIFF(DAY, TB1.DATA, TB2.DATA)) <= @IntervaloEmDias 
GROUP BY
    TB1.DIA

Browser other questions tagged sql sql-server microsoft

You are not signed in. Login or sign up in order to post.