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.PatientProfileID
where 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
