SQL query for a list of recent patient encounters

Day-to-Day Users
#1

Reports > Clients > List (e.g. interface/reports/patient_list.php) gives:
Last visit date, patient’s last name, first name, middle name, ID, street, city, state, zip, home phone, work phone.

Would anyone please be so kind as to give me the MySQL query for a similar report but that would instead give the following for a specific date:
ID, last visit date and time, facility name, encounter provider, patient’s last name, first name, middle name, date of birth, sex, reason for encounter.

I would be very grateful for this. Thank you very much inadvance.

#2

Examine the report where form_encounter.date (database field used as date of encounter) is used - probably doing a MAX or using ORDER with LIMIT 1. Then add a filter to the WHERE clause something like

form_encounter.date (<) or (<=) your date

Result should show prior encounter for a given date.

#3

Thank you for this :slight_smile:

#4

try this:

select fe.pid,pd.date,fe.facility,fe.provider_id,pd.lname,pd.fname,pd.mname,pd.DOB,pd.sex,fe.reason from form_encounter as fe INNER JOIN patient_data as pd on fe.pid = pd.pid where pd.pid=100 AND pd.date > '2022-06-07' and pd.date <'2022-06-08';

This should give all encounters for the patient with pid 100 on date 7th Jun 2022

1 Like
#5 
select fe.pid,fe.date,fe.facility,fe.provider_id,pd.lname,pd.fname,pd.mname,pd.DOB,pd.sex,fe.reason from form_encounter as fe INNER JOIN patient_data as pd on fe.pid = pd.pid where pd.pid=100 AND fe.date > '2022-06-07' and fe.date <'2022-06-08';
#6 
SELECT fe.pid,fe.date, 
	(SELECT date 
		FROM form_encounter WHERE pid = 4 
		AND date < curdate() ORDER BY date 
		DESC LIMIT 1
	) AS 'Last Visit', 
fe.facility, 
CONCAT(u.fname, ' ', u.mname, ' ', u.lname) provider, pd.lname, 
pd.fname, pd.mname, pd.DOB, pd.sex, fe.reason 
FROM form_encounter AS fe 
LEFT OUTER JOIN patient_data AS pd ON fe.pid = pd.pid
LEFT OUTER JOIN users AS u ON u.id = fe.provider_id 
WHERE fe.date = CURDATE() AND pd.pid = 4
GROUP BY fe.pid;

You should change CURDATE() to date (or dates) and delete “AND pd.pid = 4” for all patients

#7

Thank you everyone. We’ll be contributing this back to the project hopefully for inclusion as an alternative to Reports > Clients > List.

1 Like