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
;
You can try this query it will provide calendar status result.
SELECT
pdat.fname,
pdat.lname,
pc.pc_pid,
pdat.DOB,
pc.pc_eventDate,
pc.pc_startTime,
pc.pc_endTime,
pc.pc_apptstatus,
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
;
Hi Dava Muserallo,
we fixed this issue now try with this query
SELECT
pdat.fname,
pdat.lname,
pc.pc_pid,
pdat.DOB,
pc.pc_eventDate,
pc.pc_startTime,
pc.pc_endTime,
pc.pc_apptstatus,
pc.pc_title,
pc.pc_room,
pc.pc_hometext,
CONCAT(u.fname, ’ ', u.mname, ’ ', u.lname) as Provider,
insdat.type AS Ins ,
ic.name AS Carrier,
insdat.plan_name AS Program,
insdat.policy_number AS PolNr,
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
;