Active Patients Report

[ttaylor]

There is an inactive patient report which based on the last treatment date lists the patients who have not had treatment for a period of time

SQL is:-
select per.id, per.id as per_id, per.code, per.lastname, per.firstname, per.title, per.middlename, per.knownas,
per.company, per.address1, per.address2, per.address3, per.towncity, per.county, per.postcode, per.country,
per.telephone, per.worktel, per.mobile, per.fax, per.email, per.dob, per.registrationdate,

(coalesce(clinic.prefix, '') || cast(per.code as varchar(20))) as _patientcode,
per.clinic_id, clinic.name as _clinic,
per.patcat_id, patcat.name as _patient_category,
per.patstatus_id, patstatus.name as _patient_status,
per.practitioner_staff_id, staff.knownas as _practitioner,

view_nextapp.nextapp as _nextapp,
view_prevapp.prevapp as last_appointment

from per

left join clinic on (per.clinic_id=clinic.id)
left join patcat on (per.patcat_id=patcat.id)
left join patstatus on (per.patstatus_id=patstatus.id)
left join staff on (per.practitioner_staff_id=staff.id)
left join view_nextapp on (per.id=view_nextapp.per_id)
left join view_prevapp on (per.id=view_prevapp.per_id)

where (per.ispatient=true and per.deleted=false and view_nextapp.nextapp is null and view_prevapp.prevapp is not null)

We are looking to conduct a patient survey and so are looking for a list of patients who are "active" i.e. who have received treatment in the last say 12 months.

We need to exclude patients who cancelled or were DNA. So looking to select by "appointment status"

So two questions really:-

1) Managing the "active" status flag of patients
We do not currently make use of this flag and so they are currently all blank
Is this manually maintained or is there a way of automatically having this as active where a patient has had treatment in the last 12 months.


2) We are looking for a list of all patients who had treatment (aka had appointments filtered by appointment status (to remove DNAs and cancelled) giving me patient name and address details. Naturally I only need each patient to appear once on the report but could handle multiple occurences and then filter to unique in excel.

Suggestions Please
Tony Taylor
ESO Maidstone

[William]

Hi Tony

Thanks for the questions. Please find the replies to your point below:

1) For the patient's status of active and inactive it would be better to use the patient status field inside the patients record. Either way you would need to do this manually.

2) If you create a new report and use the SQL below you should be able to achieve what you require.

Code: Select all

select
app.id, start, finish, app.per_id,

case
  when (description is not null) and (description '') then description
  else per.lastname || ', ' || per.firstname || ' with ' || staff.knownas || ' [' || apptype.name || ']'
end as _description,

rtrim(per.lastname || ', ' || coalesce(per.firstname,'') || ' ' || coalesce(per.title,'')) as _patient_name,
staff_id, apptype_id, appstatus_id, app.clinic_id, room_id,
staff.knownas as _staff_name,
apptype.name as _appointment_type,
appstatus.name as _appointment_status,
clinic.name as _clinic_name,
room.name as _room_name,
app.createdon, app.createdby_staff_id, cr_staff.knownas as _createdby,
app.updatedon, app.updatedby_staff_id, up_staff.knownas as _lastupdatedby,

per.code, per.address1, per.address2, per.address3, per.towncity, per.county, per.postcode, per.email, per.telephone, per.worktel, per.mobile,

view_nextapp.nextapp,
view_prevapp.prevapp

from app
left join staff on (app.staff_id=staff.id)
left join per on (app.per_id=per.id)
left join apptype on (app.apptype_id=apptype.id)
left join appstatus on (app.appstatus_id=appstatus.id)
left join clinic on (app.clinic_id=clinic.id)
left join room on (app.room_id=room.id)
left join staff as cr_staff on (app.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (app.updatedby_staff_id = up_staff.id)
left join view_nextapp on (per.id=view_nextapp.per_id)
left join view_prevapp on (per.id=view_prevapp.per_id)

where view_prevapp.prevapp=app.start

This will only show the latest appointment for the patient and will allow you to specify the appointment status. This means you can exclude DNAs or Cancelled appointments. Also the contact details will be on the report as well.

Please let us know if you have any problems with this.

[ttaylor]

Thanks William good report.

We are now testing the output from the report to ensure we do not have any data anomalies. Report gives some answers that surprise us. That does not mean the report is incorrect; just that we have further work to do to analyse and understand the data.

[ttaylor]

Additional Fields in Active Patients Report

We are using this report to provide a list of patients to include in our annual survey of patient satisfaction. There are a few extra fields which would make the mail shot extract easier:-

1) Patient Name
We would like the ability to extract the inividual patient name fields so
Title:
First Name:
Last Name:

2) Patient Date of Birth
This will allow us to stratify the sample by age

3) Patient Sex
This will allow us to stratify the sample by sex

All the fields exist so hopefully a simple change.

Regards
Tony Taylor

[ttaylor]

Report is interesting in operation.

If you use the date option to run between dates (i.e. 1st September 2010 to August 31st 2011) it excludes all the patients who have already booked a new appointment after that date.

So you need to run it using the selection after 1st September 2010

[Support]

Hi Tony - here is the updated SQL source code to include the addtional fields :-

Code: Select all

select
app.id, start, finish, app.per_id,

case
  when (description is not null) and (description '') then description
  else per.lastname || ', ' || per.firstname || ' with ' || staff.knownas || ' [' || apptype.name || ']'
end as _description,

per.title, per.firstname, per.lastname, per.dob, per.sex,
rtrim(per.lastname || ', ' || coalesce(per.firstname,'') || ' ' || coalesce(per.title,'')) as _patient_name,
staff_id, apptype_id, appstatus_id, app.clinic_id, room_id,
staff.knownas as _staff_name,
apptype.name as _appointment_type,
appstatus.name as _appointment_status,
clinic.name as _clinic_name,
room.name as _room_name,
app.createdon, app.createdby_staff_id, cr_staff.knownas as _createdby,
app.updatedon, app.updatedby_staff_id, up_staff.knownas as _lastupdatedby,

per.code, per.address1, per.address2, per.address3, per.towncity, per.county, per.postcode, per.email, per.telephone, per.worktel, per.mobile,

view_nextapp.nextapp,
view_prevapp.prevapp

from app
left join staff on (app.staff_id=staff.id)
left join per on (app.per_id=per.id)
left join apptype on (app.apptype_id=apptype.id)
left join appstatus on (app.appstatus_id=appstatus.id)
left join clinic on (app.clinic_id=clinic.id)
left join room on (app.room_id=room.id)
left join staff as cr_staff on (app.createdby_staff_id = cr_staff.id)
left join staff as up_staff on (app.updatedby_staff_id = up_staff.id)
left join view_nextapp on (per.id=view_nextapp.per_id)
left join view_prevapp on (per.id=view_prevapp.per_id)

where view_prevapp.prevapp=app.start


[ttaylor]

Thank you this works well.

[ttaylor]

We wish to add another standard field to this report;-

Registration Date
as shown on the patient details record under "Other Details"
(screen capture attached)

Regards
Tony Taylor

[ttaylor]

Curiosity Question

Clinic Office has fields for such things as:-

Last Treatment date
Previous Treatment date

Is there anything that shows "first treatment date"?

Tony Taylor
ESO Maidstone

[Support]

Not as standard - most customers use "registration date" as the date that a patient joined the clinic. If you need us to add the first appointment date to a report then please contact our help desk and we'll take a look at what you're trying to achive and see if we can help.

[ttaylor]

Many thanks for the insight. On reflection we think we can use Registration Date to provide the information that we need in our reporting.

So can I ask for the Source Code of our Active Patients Report be modified to include that field in the column chooser.

Regards
Tony Taylor
Maidstone

[ttaylor]

Change Request to add Registration Date to our existing Acting Patient Report

My research team have asked for this change to be made as quickly as possible. So we are happy to pay the report cost in order to expedite

Regards
Tony Taylor

[Daniel Casserly]

Hi there,

If you just wish to add this field to your report you can add the following SQL to the list of fields:

Code: Select all

per.registrationdate,

I would suggest putting this in between the lines that say :

Code: Select all

per.mobile,

view_nextapp.nextapp,

You will then be able to search on that field and it will appear by default on the grid.

Is this what you were looking for?

[ttaylor]

Thanks Daniel just what we needed