CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
inefficientmkts
inefficientmkts

Reputation: 89

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: 

TempTable1  
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 

Select 
        PatientName
       ,/*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.

Upvotes: 0

Views: 79

Answers (4)

gregory.skoczylas
gregory.skoczylas

Reputation: 11

This my proposition. Code is simple and is based on the fact 6 weeks=42 days. I hope that I helped

   WITH TMP_CTE AS(
    Select 
            PatientName,
        VisitDate,
        LEAD(VisitDate, 2) OVER(PARTITION BY PatientName ORDER BY VisitDate) AS OffsetVisitDate
            DATEDIFF(day, VisitDate, LEAD(VisitDate, 2) OVER(PARTITION BY PatientName ORDER BY VisitDate)) as Difference_in_Days
    FROM TempTable1
    )

    SELECT DISTINCT PatientName AS Patient_who_has_not_3visits
    FROM TMP_CTE WHERE PatientName NOT IN
    (
    SELECT DISTINCT PatientName AS Patient_who_has_3visits_within_6week
    FROM TMP_CTE
    WHERE Difference_in_Days>42
    )

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95082

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
  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
);

Upvotes: 2

Serg
Serg

Reputation: 22811

Let's look at this from the other side. We'll check if minimum visit date within 3 preceding visits (current included) is within 6 weeks from current visit date.

with dat as (
  -- sample data
  select *
  from (
    values 
     ('Steve',cast('11/1/2016' as date))
    ,('Steve',cast('11/2/2016' as date))
    ,('Steve',cast('11/3/2016' as date))
    ,('Pete ',cast('11/1/2016' as date))
    ,('Pete ',cast('11/2/2016' as date))
    ,('Dave ',cast('2/3/2016' as date)) 
    ,('Dave ',cast('11/2/2016' as date))
    ,('Dave ',cast('2/2/2016' as date))
    )t(PatientName, VisitDate) 
), cte as(
 select PatientName
    , md=datediff(week
                  ,min(VisitDate) over(partition by PatientName order by VisitDate 
                                       rows between 2 preceding and current row)
                   ,VisitDate)
    , n = count(*) over(partition by PatientName order by VisitDate 
                      rows between 2 preceding and current row)                                                                      
  from dat
)
select PatientName
from cte
group by PatientName
having max(n)<3 or max(md)>6

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270773

This is challenging. I think that cross apply might be the simplest method to implement:

select t.*, tt.numvisits
from temptable1 t cross apply
     (select count(*) as numvisits
      from temptable t2
      where t2.patientname = t.patientname and
            t2.visitdate >= t.visitdate and
            t2.visitdate < dateadd(week, 6, t.visitdate)
     ) tt;

Then, if you want to find the people who don't have three visits:

select t.patientname
from temptable1 t cross apply
     (select count(*) as numvisits
      from temptable t2
      where t2.patientname = t.patientname and
            t2.visitdate >= t.visitdate and
            t2.visitdate < dateadd(week, 6, t.visitdate)
     ) tt;
group by t.patientname
having max(numvisits) < 3;

Note that the cross apply can take advantage of an index on temptable(patientdate, visitdate). If your data is not too big, then performance may even be reasonable.

Upvotes: 2

Related Questions