Active Patients Report

Help with reports & document templates
Post a reply

Active Patients Report

Postby ttaylor » August 4th, 2011, 8:47 am

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
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby William » August 12th, 2011, 2:11 pm

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.
ClinicOffice Support Team
User avatar
William
 
Posts: 159
Joined: November 1st, 2007, 8:52 am
Top

Re: Active Patients Report

Postby ttaylor » August 22nd, 2011, 10:17 am

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
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby ttaylor » September 23rd, 2011, 8:57 am

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
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby ttaylor » September 23rd, 2011, 9:00 am

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
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby Support » September 29th, 2011, 12:42 pm

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
ClinicOffice Support Team
User avatar
Support
Site Admin
 
Posts: 874
Joined: August 25th, 2005, 6:37 pm
Top

Re: Active Patients Report

Postby ttaylor » September 30th, 2011, 1:58 pm

Thank you this works well.
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby ttaylor » February 22nd, 2013, 3:03 pm

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
Attachments
Registration Date
Registration Date.PNG (8.76 KiB) Viewed 4037 times
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby ttaylor » February 22nd, 2013, 3:06 pm

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
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby Support » February 25th, 2013, 8:17 am

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.
ClinicOffice Support Team
User avatar
Support
Site Admin
 
Posts: 874
Joined: August 25th, 2005, 6:37 pm
Top

Re: Active Patients Report

Postby ttaylor » February 26th, 2013, 8:45 am

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
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby ttaylor » March 7th, 2013, 8:50 am

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
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top

Re: Active Patients Report

Postby Daniel Casserly » March 11th, 2013, 1:06 pm

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?
ClinicOffice Support Team
User avatar
Daniel Casserly
 
Posts: 49
Joined: May 25th, 2010, 3:34 pm
Top

Re: Active Patients Report

Postby ttaylor » March 13th, 2013, 11:25 am

Thanks Daniel just what we needed
ttaylor
 
Posts: 72
Joined: April 26th, 2010, 8:13 am
Location: ESO Maidstone
Top


Post a reply

Return to Reports & Templates

Who is online

Users browsing this forum: No registered users and 1 guest

cron