Need help on SQL date difference

Question

Hello, I have below dates and need to find out all records which are minimum 60days a part from each other.

Sample Data

ID Date
4 2018-03-26 00:00:00.000
4 2018-05-21 00:00:00.000
4 2018-06-25 00:00:00.000
4 2018-09-17 00:00:00.000
4 2019-05-13 00:00:00.000
4 2019-06-17 00:00:00.000
4 2019-07-22 00:00:00.000
4 2019-12-09 00:00:00.000
4 2021-03-03 00:00:00.000

Expected result
ID Date
4 2018-03-26 00:00:00.000
4 2018-06-25 00:00:00.000
4 2018-09-17 00:00:00.000
4 2019-05-13 00:00:00.000
4 2019-07-22 00:00:00.000
4 2019-12-09 00:00:00.000
4 2021-03-03 00:00:00.000

Any help would be appreciated.

Answer 1

This query seems to give the expected results:

; with CTE as
(
    select top(1) * 
    from MyTable 
    order by [Date]
    union all
    select t.* 
    from CTE
    inner join MyTable t on datediff(day, CTE.[Date], t.[Date]) >= 60
    where not exists (select * from MyTable where datediff(day, CTE.[Date], [Date]) >= 60 and [Date] < t.Date)
)
select * 
from CTE
order by [Date]
option (maxrecursion 0)

Answer 2

Sorry, I want to know which dates you are referring to. For example, for 2018-03-26, need to calculate the interval between it and 2018-05-21, or need to calculate the interval between it and each subsequent date.

Maybe this is what you want

CREATE TABLE #test(ID INT,[Date] DATE)  
INSERT INTO #test VALUES  
(4,'2018-03-26 00:00:00.000'),  
(4,'2018-05-21 00:00:00.000'),  
(4,'2018-06-25 00:00:00.000'),  
(4,'2018-09-17 00:00:00.000'),  
(4,'2019-05-13 00:00:00.000'),  
(4,'2019-06-17 00:00:00.000'),  
(4,'2019-07-22 00:00:00.000'),  
(4,'2019-12-09 00:00:00.000'),  
(4,'2021-03-03 00:00:00.000')  
  
;WITH cte  
as(SELECT *,LEAD([Date],1) OVER(ORDER BY [Date]) [Date2]  
FROM #test)  
  
SELECT *,ABS(DATEDIFF(day,[Date],[Date2])) datedif  
FROM cte  
WHERE ABS(DATEDIFF(day,[Date],[Date2]))>60  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.