Viewing 14 posts - 1 through 14 (of 14 total)
  • Author
    Posts
  • #31242

    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

    #32844

    William
    Keymaster

    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.

    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 || ' '
    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.

    #32845

    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.

    #32846

    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

    #32847

    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

    #32848

    Support
    Keymaster

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

    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 || ' '
    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
    
    #32849

    Thank you this works well.

    #32850

    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

    #32851

    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

    #32852

    Support
    Keymaster

    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.

    #32853

    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

    #32854

    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

    #32855

    Hi there,

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

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

    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?

    #32856

    Thanks Daniel just what we needed

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.