We created a query to pull session/encounter data from the EHR on a daily basis. What I’ve found in application is that it is not pulling the calendar status to let us know whether the appointment was kept or not. Otherwise, it puts the session data in there as if the appointment happened even if it didn’t. I’ve included the query below for the session pull that needs the status added. Any help appreciated!
SELECT
pdat.fname,
pdat.lname,
pc.pc_pid,
pdat.DOB,
pc.pc_eventDate,
pc.pc_startTime,
pc.pc_endTime,
pc.pc_title,
pc.pc_room,
pc.pc_hometext,
CONCAT(u.fname, ’ ', u.mname, ’ ', u.lname) Provider,
insdat.type AS "Ins ",
ic.name AS “Carrier”,
insdat.plan_name AS “Program”,
insdat.policy_number AS “Pol Nr”,
pdat.procode AS “Class”
FROM
insurance_data
insdat
inner join patient_data
pdat on insdat.pid = pdat.pid
inner join insurance_companies
ic ON ic.id = insdat.provider
LEFT JOIN
openemr_postcalendar_events pc ON pdat.pid = pc.pc_pid
LEFT JOIN users u ON u.id = pc.pc_aid
WHERE pc.pc_eventDate = “2022-10-10”
ORDER BY pc.pc_startTime ASC
;