Notifications
Clear all
Topic starter
We use SQL CPS and Oracle EMR, and are trying to capture the amount of time a patient spends in our office for a typical visit. This means check in to check out...
Is there a consensus on how to get this info? Has anyone developed a good report set? Can it be done with an EMR Inquiry I wonder?
Any ideas would be welcome.
Posted : June 27, 2012 2:41 am
We've been using this query in PM/CPS for a few years (with a couple of tweaks -- it's called from Visual FoxPro, so there were a couple of VFP-specific things I removed).
select dff.ListName as Facility, dfd.ListName as Doctor
, dfr.ListName as Resource, dfp.ListName as ResponsibleProvider
, pp.PatientId
, ci.Created as CheckIn, ap.ApptStart, co.Created as CheckOut, ap.ApptStop
from ActivityLog ci
join ActivityLog co on ci.PatientProfileId = co.PatientProfileId
and ci.RecordId = co.RecordId
and ci.TableName = co.TableName
join Appointments ap on ap.AppointmentsId = ci.RecordId
join DoctorFacility dfd on ap.DoctorId = dfd.DoctorFacilityId
join DoctorFacility dfr on ap.ResourceId = dfr.DoctorFacilityId
join DoctorFacility dff on ap.FacilityId = dff.DoctorFacilityId
join PatientProfile pp on ci.PatientProfileId = pp.PatientProfileId
join DoctorFacility dfp on pp.DoctorId = dfp.DoctorFacilityId
where ci.TableName = 'Appointments'
and ci.Value2 like 'Checked in%'
and co.Value2 like 'Checked out%'
and ci.ActivityLogId < co.ActivityLogId
and ci.Created >= @DateBegin
and ci.Created < DateAdd(d, 1, @DateEnd)
and ci.Created = (select min(ci1.Created)
from ActivityLog ci1
where ci1.RecordId = ci.RecordId
and ci1.Value2 like 'Checked in%'
)
and co.Created = (select max(co1.Created)
from ActivityLog co1
where co1.RecordId = co.RecordId
and co1.Value2 like 'Checked out%')
order by dff.ListName, dfd.ListName, dfr.ListName, dfp.ListName
, ap.ApptStart, ci.PatientProfileId
Posted : June 27, 2012 3:36 am
Sarek, I was trying to use your query (running in SQL Mgmt Studio) and got a message that I must declare @DateBegin. Can you tell me how to fix that?
Posted : July 10, 2012 2:12 pm
declare @DateBegin as datetime
, @DateEnd as datetime
set @DateBegin = '6/1/2012'
set @DateEnd = '6/30/2012'
select dff.listname etc.
Posted : July 11, 2012 12:45 am
Do you have Value Pack? There is a "Time Between Appointment Statuses" report under Value Pack > Appointments that is pretty handy...
Posted : September 27, 2012 9:17 am