question

mayur avatar image
0 Votes"
mayur asked mayur commented

Need help on SQL date difference

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.

sql-server-generalsql-server-transact-sqlazure-sql-database
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @mayur

What do you mean by 60 days apart? Which two dates are 60 days apart?

0 Votes 0 ·

I mean minimum difference between dates should be 60 days

0 Votes 0 ·

4 2018-03-26 00:00:00.000
4 2018-05-21 00:00:00.000 --does not qualify as difference is only 56 days
4 2018-06-25 00:00:00.000-- so we have to select this one directly after first date
4 2018-09-17 00:00:00.000--qualify
4 2019-05-13 00:00:00.000--qualify
4 2019-06-17 00:00:00.000--does not qualify as difference is only 35 days from last qualifid date
4 2019-07-22 00:00:00.000-- so we have to select this after 2019-05-13
4 2019-12-09 00:00:00.000--qualify
4 2021-03-03 00:00:00.000--qualify

0 Votes 0 ·

4 2018-03-26 00:00:00.000
4 2018-05-21 00:00:00.000 --does not qualify as difference is only 56 days
4 2018-06-25 00:00:00.000-- so we have to select this one directly after first date
4 2018-09-17 00:00:00.000--qualify
4 2019-05-13 00:00:00.000--qualify
4 2019-06-17 00:00:00.000--does not qualify as difference is only 35 days from last qualifid date
4 2019-07-22 00:00:00.000-- so we have to select this after 2019-05-13
4 2019-12-09 00:00:00.000--qualify
4 2021-03-03 00:00:00.000--qualify

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered mayur commented

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)


· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you so much

0 Votes 0 ·

If I want to use ID as other ID also has same sort of dates where can I plug in my ID column ?

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered mayur commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Echo, Not a subsequent dates but minimum 60 days difference from each qualified date meaning after 2018-03-26 00:00:00.000 next qualified date would be
2018-06-25 00:00:00.000 so now start date for difference should be 2018-06-25 00:00:00.000 in order to find next date.

0 Votes 0 ·