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.
8,490 questions
{count} votes

Accepted answer
  1. Viorel 82,476 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: Most helpful
  1. EchoLiu-MSFT 14,416 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.