Count 3 Consecutive Appointments with Certain Criteria 2

[beth4530]

I have to identify clients with 3 consecutive appointments. The client had to have shown (Status="SH") or completed (status="CO")the appointment for it to count. The tricky part is I need to skip the count for appointments that were canceled(status="CA") because of any reason other than a late cancellation (cancellation_reason ="LC"). Below is my current formula.


(Previous ({Patient_Clin_Tran.patient_id})= {Patient_Clin_Tran.patient_id}
and
({Patient_Clin_Tran.status} in [ "CO" ,"SH"] and
previous({Patient_Clin_Tran.status})in [ "CO" ,"SH"] and

next({Patient_Clin_Tran.patient_id})= {Patient_Clin_Tran.patient_id} and
next({Patient_Clin_Tran.status})in [ "CO" ,"SH"] )) = true

 

[JonFer]

What do you mean by consecutive appointments? 3 apppts the same day immediately following each other? An appt each day for 3 days in a row? 3 appts per week or month?

 

[beth4530]

A clients can have more than one apppointment a week or only once a month or once every three months. The schedule depends on the client.

Med Appt 1/2/2012 Completed
Med Appt 1/5/2012 No Show
Med Appt 2/8/2012 Completed
Med Appt 2/15/2012 Completed

This client would not be counted because of the No Show on 1/5/2012

 

[JonFer]

Assuming your appt dates include the time, one option is to write a query that gives you the start/end dates of the "Cancelled except for Late Cancel" appts for each patient and then use it to restrict your counts. This is untested so verify it's doing what it should before using for the final query.

(Select A.PatientID, A.ApptDate as StartDate, IsNull((Select Min(B.ApptDate) from Appointments B
Where B.PatientID=A.PatientID and B.ApptDate > A.ApptDate and B.Status = 'CA' and B.CancelRsn != 'LC'),'2999-12-31') as EndDate
From Appointments A
Where A.Status = 'CA' and A.CancelRsn != 'LC'
Union
Select A.PatientID, '1900-1-1', IsNull((Select Min(B.ApptDate) from Appointments B
Where B.PatientID=A.PatientID and B.Status = 'CA' and B.CancelRsn != 'LC'),'2999-12-31')
From Appointments A
Group By A.PatientID
) as D

The left join from the appointments table to this derived table then would be something like this:

Select C.PatientID, Min(C.ApptDate) as FirstDate, Max(C.ApptDate) as LastDate
From Appointments C Left Join (Select...) as D On C.PatientID=D.PatientID
Where C.ApptDate Between D.StartDate and D.EndDate And C.Status In ('CO', 'SH')
Group By C.PatientID
Having Count(C.PatientID) > 2

 

[markros]

;with cte as (select *, row_number() over (partition by Client order by ApptDate) - row_number() over (partition by Client, case when status IN ('SH','CO') then 1 else 2 end order by AppDate) as Grp)

select Client, count(*) as Appointments
from cte
GROUP BY Client, Grp
HAVING COUNT(*) >=3 -- 3 consecutive non-cancelled appointments

The idea of my untested solution is based on common solution for finding gaps and islands type of problem. See, for example, this blog post by Plamen Ratchev

http://pratchev.blogspot.com/2010/02/refactoring-ranges.html

PluralSight Learning Library

 

[JonFer]

markros - that's a nice solution - a little abstract but very efficient. Probably helpful to add a Min(ApptDate) to distinguish multiple appearances of a ClientID.

A correction to my option: the final Group By should include PatientID and StartDate.

 

[markros]

In the cte definition I'm missing FROM Appointments.

PluralSight Learning Library