Last appointment by Probram SQL Command

[mark12010]

I have a report in that list all clients for a specified outpatient (recurring episode) program, last appointment, who they saw, and how many days since they were last seen. The report works as it is but it is grouped by Patient Name. When I try to group it by clinician it gives me inaccurate, duplicate, or missing data. For example there is a formula that displays “NO APPT. DATA ON FILE” so when I group my clinician all of these drop off
I received an example of this report in an SQL Command. It wouldn’t run with our current version of Cache. We have since upgraded and the command works. The only thing I changed is to Suppress the Group Header. See the command below:
*****************************************************************************************************************
select
EP.patid
, EP.v_patient_name
, EP.episode_number
, EP.date_of_discharge
, EP.preadmit_admission_date
, EP.program_value
, APPQUERY.AppointmentStart
, APPQUERY.service_code
, APPQUERY.staffid
, APPQUERY.staff_name
, APPQUERY.site_name
, APPQUERY.MaxAppt
from episode_history EP
left outer join
( select
APP.PATID
, APP.episode_number
, cast(string(cast(APP.appointment_date as varchar), ' ', cast(convert(time, APP.appointment_start_time) as varchar)) as datetime) as "AppointmentStart"
, APP.service_code
, APP.staffID
, APP.staff_name
, APP.site_name
, MAXAPP.MaxAppt
from
appt_data APP
inner join
(select
patid
, episode_number
, max(cast(string(cast(appointment_date as varchar), ' ', cast(convert(time, appointment_start_time) as varchar)) as datetime)) as "MaxAppt"
from appt_data
where program_code = '{?Program}'
group by patid, episode_number) as MAXAPP on
((APP.patid = MAXAPP.patid) and (APP.episode_number = MAXAPP.episode_number) and (cast(string(cast(APP.appointment_date as varchar), ' ', cast(convert(time, APP.appointment_start_time) as varchar)) as datetime) = MAXAPP.MaxAppt))
) as APPQUERY on ((EP.patid = APPQUERY.patid) and (EP.episode_number = APPQUERY.episode_number))
where
EP.program_code = '{?Program}' and
EP.date_of_discharge is null
****************************************************************************************************************
Like I said this works, however, the output is
Client Name ID Episode Last Appointment Service Site Staff Name
It lists the Last Appointment as Daye & Time I only need date as I need a formula to give me days since last appointment e.g. shared Numbervar Days;
Days := DateDiff ("d",{billing_tx_history.date_of_service}, CurrentDate);
It lists appts from newest to oldest however it is also listing future appts.
I also need to see clients with no appts so I can use the formula:
IF ISNULL ({billing_tx_history.date_of_service})
THEN "NO APPT DATA ON FILE"
So what I am looking for is the following output:
Client PATID Episode Last appt Service Clinician Days
John Doe 012 1 11/29/2012 AIP45 Mary Smith 181
Jane Doe 1223 1 NO APPT DATA ON FILE

 

[Charliy]

I would put this in the Crystal Repository section on the Netsmart Communnity forum. There are plenty of people there familiar with the Avatar database including a few SQL experts.