CODEWITHSUNDEEP
sql-server
mmoffet
mmoffet

Reputation: 13

Select Recurrences Only If At Least X Days Past the Previous

I need some help structuring a query to only pull back recurrences that are after a set number of days, in my case 30.

My table structure is as follows: 

PatientID  Date  
1          2015-09-01  
1          2015-09-03  
2          2015-03-04  
2          2015-03-07  
2          2015-09-15

In this example, I only want to return rows 1, 3, and 5.

I tried doing a left join on itself, where the date in the second is > DATEADD(D,30,Date).

My other thought was a recursive CTE with the first query pulling the min date for each patient then a union where the table date was at least 30 days greater than the max of each patients CTE date but you can't have a max in the join statement.

I'm pretty stumped. Any advice would be greatly appreciated.

Upvotes: 1

Views: 36

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31775

This is how I would do it:

SELECT * FROM MyTable t1
WHERE NOT EXISTS(
 SELECT * FROM MyTable t2
 WHERE t1.PatientId=t2.PatientId
 AND t2.Date < t1.Date
 AND DATEDIFF(dd, t2.Date, t1.Date) < 30
)
ORDER BY t1.PatientId, t1.Date ASC

Upvotes: 2

Ted Elliott
Ted Elliott

Reputation: 3493

I think something like this should work (notepad coding here, so the syntax may be a little off)

WITH CTE(
   SELECT PatientId, Min(Date) as Date
  FROM MyTable
  Group BY PatientId)
SELECT A.*
FROM MyTable A
LEFT OUTER JOIN CTE CTE
  ON A.PatientId = CTE.PatientId
  AND (A.Date = CTE.Date OR A.Date > DATEAdd(dd, 30, CTE.Date)
WHERE CTE.PatientId IS NOT NULL

Upvotes: 0

Related Questions