I need to find all patients who have not had 3 visits within a 6 week period during a rolling year.
I’ll start from a temp table that has all results for 1 year period, like this:
,/*most recent VisitDate*/
,ROW_NUMBER() OVER (Partition By PatientName ORDER BY VisitDate desc) AS RowNumber
Where /*PatientName has <3 visits within any rolling 6 week period */
A person got three visits within six weeks, when we find a visit where the previous visit and the following visit are no more than six weeks apart. We can use
LEAD to find the previous and the following visits to a visit.
select patientname from (select distinct patientname from temp) patients where patientname not in ( select patientname from ( select patientname, lag(visitdate) over (partition by patientname order by visitdate) as visit1, lead(visitdate) over (partition by patientname order by visitdate) as visit3 from temp ) visits where datediff(day, visit1, visit3) <= 42 -- 42 days = 6 weeks );