Need help on SQL date difference

Mayur 41 Reputation points
2021-10-20T02:33:04.827+00:00

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.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,175 questions
Accepted answer
  1. Viorel 89,236 Reputation points
    2021-10-20T05:50:25.323+00:00

    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)

1 additional answer

Sort by: Oldest
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,501 Reputation points
    2021-10-20T05:29:14.47+00:00

    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.