Patient Retention by Staff Report

[William]

The “Patient Retention by Staff” report will indicate if a patient has been retained or not. This is based on whether the patient has had a subsequent appointment after their initial appointment. You can specify the date range of that initial appointment and the staff member they had. The report by default is grouped by that staff member and it automatically excludes cancelled appointments. This will give you an idea of which staff are retaining their patients and the number of new patients they have seen within a given time.

Before importing this report, you will need to create two custom views for the report to work. Please follow these steps:-

1) Go to the Tools menu (tab) and click Advanced DB Operation

2) Make sure the “As Administrator” checkbox is ticked

3) Delete any code present in this window and copy & paste the code below into the “Advanced Database Operation” window

 

create view xview_first_appt as

select

app.per_id, min(app.start) as datetime

from app

left join appstatus on (app.appstatus_id=appstatus.id)

where (appstatus.cancelled=false or app.appstatus_id is null)

group by app.per_id;

ALTER TABLE xview_first_appt

  OWNER TO admin;

GRANT ALL ON TABLE xview_first_appt TO admin;

GRANT ALL ON TABLE xview_first_appt TO std_user;

 

4) Click Execute, OK

5) Delete the code and copy & paste the code below

 

create view xview_count_after_exam as

select

(cast(count(app.id) as int) -1) as appt_count, app.per_id

from app

left join appstatus on (app.appstatus_id=appstatus.id)

where (appstatus.cancelled=false or app.appstatus_id is null)

group by app.per_id;

ALTER TABLE xview_count_after_exam

  OWNER TO admin;

GRANT ALL ON TABLE xview_count_after_exam TO admin;

GRANT ALL ON TABLE xview_count_after_exam TO std_user;

 

6) Click Execute, OK and then Close

Now that you’ve created the required database VIEWS, please download and install the attached report file.

For instructions on how to import the downloaded report, please see the article via the following link:-
https://pioneersoftware.co.uk/forums/topic/how-do-i-import-a-report-file-into-my-database

 

[Author]

Posts