inefficientmkts inefficientmkts - 8 months ago 40
SQL Question

How to find number of occurrences that don't have (3) instances within defined time frame period (6 weeks)

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:

PatientName VisitDate
Steve 11/1/2016
Steve 11/2/2016
Steve 11/3/2016
Pete 11/1/2016
Pete 11/2/2016
Dave 2/3/2016
Dave 11/2/2016
Dave 2/2/2016

But I still need to

,/*most recent VisitDate*/
,ROW_NUMBER() OVER (Partition By PatientName ORDER BY VisitDate desc) AS RowNumber
FROM TempTable1
Where /*PatientName has <3 visits within any rolling 6 week period */

The result should be "Pete" and "Dave" since both do not have three visits within six week period. Steve does 3 visits within 6 weeks.

I could not find any good examples where they looked for more than once instance within a time frame. Thank you many times in advance. I'll be sure to leave great rating.


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 LAG and 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
      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