Query Help Needed

Day-to-Day Users
#1

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
;

#2

Not seeing pc.pc_apptstatus in the SELECT portion.

#3

HI Dava Muserallo ,

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
;

Thanks
Param
help@capminds.com

#4

I get the following error when I execute the updated query.

Query Error
Query Error956×740 27.9 KB

#5

Hello Dava

Please try to fix the single quotes used inside CONCAT function correctly - this may fix the issue.

Its because of the quotes gets changed in this forum editor like that - and you must have copied the query from here.

Thank You
Infeg Team
services@infeg.com

#6

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
;

Thanks
Param
help@capminds.com