How to perform a query on the Microsoft SQL SERVER that a days-ahead set returns only the dates that it encounters with that existing difference. for example
In a table have the following dates:
02/01/2018
06/01/2018
08/01/2018
15/01/2018
21/01/2018
26/01/2018
28/01/2018
And the time period is set to 4 days it only shows 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
The result example assumes that the query should return all rows where the date difference between the current row and the previous row or row is within the range.
Here is a 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;
Replace DataX with the column name that contains the date and tbDatas with the table name.
Here is the code that generated the data mass 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));
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