General Query Issue

Day-to-Day Users
#1

We have several queries that pull data related to encounters for us, and I find that if there is no insurance (ie, client is self/cash pay or pays on a sliding scale), those clients do not show up in the data pulls. I’m finding that seems to be the common identifier as of now, so I thought I would get feedback on how to address this.

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
;

#2

Hi Dava Muserallo,

You can try this query it will provide 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) 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

#3

A pro tip for pasting code or SQL here on the discourse forums: you can wrap the code in 3 back ticks and syntax highlighting and other features will kick in. Below is an example, I have three back ticks followed immediately by “sql” indicating the code type and then a new line with the SQL and close with 3 more back ticks on a new line. It makes for a bit better aesthetics.

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;
1 Like