Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Count 3 Consecutive Appointments with Certain CriteriaHelpful Member!(2) 

beth4530 (TechnicalUser) (OP)
8 Mar 12 16:10
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}
({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 (Programmer)
8 Mar 12 21:11
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 (TechnicalUser) (OP)
9 Mar 12 8:17
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

Helpful Member!  JonFer (Programmer)
11 Mar 12 23:23
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'
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
Helpful Member!  markros (Programmer)
11 Mar 12 23:39


;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

PluralSight Learning Library

JonFer (Programmer)
12 Mar 12 1:52
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 (Programmer)
12 Mar 12 8:58
In the cte definition I'm missing FROM Appointments.

PluralSight Learning Library

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close