Notifications
Clear all
Topic starter
I am trying to modify the report below to show only the appointment statuses of no show, checked out and canceled. I want to tie in Reasons - Cancel/Patient, Cancel/Doctor, Bad Weather, Pt in Hospital, Pt going out of town, Hospital. I am thinking that I can just pull on the reasons I just listed and that would take care of the status of canceled. That would leave just checked out and no show.
I am not sure of which fields to use . Any help would be appreciated. Thank you.
/* Canceled Appointments */
SET NOCOUNT ON
SELECT ap.ApptStart,
aset.ApptSetId,
dbo.asGetApptSetName(ISNULL(aset.ApptSetId,0),ap.AppointmentsId) ApptSetName,
ac.ApptChainId,
ac.Name ApptChainName,
ml.Description as Status,
ap.FacilityID,
ap.OwnerID,
ap.ApptKind,
ap.ResourceID,
at.Name as Type,
df2.ListName AS Facility,
ISNULL(pp.Last,'')+', '+ISNULL(pp.First,'') AS PatientName,
pp.PatientID,
StartHour=convert(varchar(2),datepart(hour,ap.ApptStart)),
StartMinute=convert(varchar(2),datepart(minute,ap.ApptStart)),
df.ListName AS Resource
FROM Appointments ap
LEFT JOIN ApptChain ac ON ap.ApptChainId = ac.ApptChainId
LEFT JOIN ApptSet aset ON ap.ApptSetId = aset.ApptSetId
LEFT JOIN Medlists ml ON ap.ApptStatusMId = ml.MedlistsId
LEFT JOIN ApptType at ON ap.ApptTypeID = at.ApptTypeId
LEFT JOIN DoctorFacility df ON ap.ResourceID = df.DoctorFacilityID
LEFT JOIN DoctorFacility df2 ON ap.FacilityID = df2.DoctorFacilityID
LEFT JOIN PatientProfile pp ON ap.OwnerID = pp.PatientProfileID
WHERE ap.ApptKind = 1 AND
isnull(ap.Canceled,0)<>0 AND
ap.ApptStart >= ISNULL(?DATERANGE.DATE1?,'1/01/1900') AND
ap.ApptStart < dateadd(day,1,ISNULL(?DATERANGE.DATE2?,'1/01/3000'))
AND --Filter on resource
(
(?RESOURCE.ITEMDATA? IS NOT NULL AND ap.ResourceID IN (?RESOURCE.ITEMDATA.U?)) OR
(?RESOURCE.ITEMDATA? IS NULL)
)
AND --Filter on facility
(
(?FACILITY.ITEMDATA? IS NOT NULL AND ap.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
(?FACILITY.ITEMDATA? IS NULL)
)
AND --Filter on reason
(
(?REASON.ITEMDATA? IS NOT NULL AND ap.ApptStatusMID = ?REASON.ITEMDATA.U?) OR
(?REASON.ITEMDATA? IS NULL)
)
Posted : February 7, 2013 4:27 am
