Notifications
Clear all
Topic starter
Okay, building a query to determine the time difference between when the patient is "arrived" and when an office visit was started. I've done similar things in Oracle but can't for the life of me figure out where CPS is storing the "Appointment Activity List" data. This data (visible by going to VIEW->APPT ACTIVITY LIST) has the recorded status and change times of a particular appointment. Where are they storing this info?
Posted : February 20, 2013 9:21 am
Good Evening - Did you ever complete your query to do this? I would be very interested in seeing what you came up with to accomplish calculating the time difference. Thank you!
Posted : January 1, 2016 5:46 pm
Topic starter
Oh, you bet. Long ago. I've never had much luck posting code into this forum. It tends to dump you on your ear. I haven't tried it in a long time though so here goes...
-- Appt Delta
select P.Searchname Patient
, A.ApptStart ApptScheduled
, L.Created ArrivalTime, D.DB_Create_Date EncounterStart
, convert(datetime,D.ObsValue) VisitStart
, D.Summary
,(DateDiff(n,case when L.Created > A.ApptStart
then L.Created
else A.ApptStart end ,convert(datetime,D.ObsValue)))
as UnRounded
,case when (DateDiff(n,case when L.Created > A.ApptStart
then L.Created
else A.ApptStart end ,convert(datetime,D.ObsValue))) < 0
then 0
else DateDiff(n,case when L.Created > A.ApptStart
then L.Created
else A.ApptStart end ,convert(datetime,D.ObsValue))
end as Delta
from ActivityLog L
Join PatientProfile P on L.PatientProfileID = P.PatientProfileID
Join Appointments A on A.AppointmentsID = L.RecordID
Join (
select obs.HDID,obs.obsvalue, document.PID, document.SDID,document.summary,document.db_create_date
from document
join obs on obs.sdid = document.sdid
Where Document.DocType='1'
and Obs.HDID = '6299'
and (isdate(obs.obsvalue)) = 1
) as D on D.PID=P.PID
Join
(
select min(created) TimeStamp,PatientProfileID
from activitylog x
where x.Value2='Arrived'
and x.Created > dateadd(d,-7,GetDate())
and x.Created < dateadd(d,-6,GetDate())
group by x.PatientProfileID
) as L2 on L2.PatientProfileID = L.PatientProfileIDwhere L.Created > dateadd(d,-7,GetDate())and L.Created < dateadd(d,-6,GetDate())
and D.db_create_date > dateadd(d,-7,GetDate())
and D.db_create_date < dateadd(d,-6,GetDate())
and FunctionName = 'Change Appointment Status'
and L.value2='Arrived'
and L.Created = L2.TimeStamp
order by delta desc
This produces some some obvious columns then there is an oddity that I can explain. We have a unique practice where our clinicians only see 10 to 12 patients per day. One of our many goals is to never have a patient wait. This results in patients often arriving early and being seen before their scheduled visit resulting in a negative number. Some math was done to round these UP to Zero as shown in teh Delta column. This is done because we end up submitting this data to a partner where we are held accountable for some odd metrics in a "fee for service" model. Happy to break this down or tweak it for you should you require it. I currently have this attached to an Excel Spreadsheet of a bunch of these metrics for whoever uses it to report from. Feel free to contact me at [email protected] if you have any further questions.
Posted : January 4, 2016 8:12 am
Below is the report I generated to calculate the cycle times.
select pp.patientid, pp.first + ' ' + pp.last as 'Patient Name', df.listname as LOC, df1.listname as Provider, a.apptstart, al.value2 as Apptstatus, al.created as 'Check In TIme', al2.value2 as Apptstatus, al2.created as 'Checkout Time', datediff(minute,al.created,al2.created) as 'Cycle Time in Minutes'
from patientprofile pp
inner join appointments a on pp.patientprofileid = a.ownerid
left join ActivityLog al on a.appointmentsid = al.recordid and al.value2 = 'Arrived' and al.functionname = 'Change Appointment Status'
left join ActivityLog al2 on a.appointmentsid = al2.recordid and al2.value2 in ('Checked Out') and al2.functionname = 'Change Appointment Status'
left join doctorfacility df on a.facilityid = df.doctorfacilityid
left join doctorfacility df1 on a.doctorid = df1.doctorfacilityid
where a.apptstart between '05/01/2016' and '05/31/2016'
Posted : May 24, 2016 9:41 am
This is perfect! Thank you!
Posted : May 25, 2016 5:30 am
