Date format Error on Query

Day-to-Day Users
#1

Hello!

I am VERY new to OpenEMR and queries in it. I found a format in the community (thank you!) that is close to what I am trying to accomplish, however, when I run it - it does not like the date. I have tried different variations and still receive the following: “Error in query (1054): Unknown column ‘‘2022’ in ‘where clause’” - Can anyone help? See below.

Thank you!

SELECT
patient_data.fname,
patient_data.lname,
openemr_postcalendar_events.pc_pid,
patient_data.DOB,
openemr_postcalendar_events.pc_eventDate,
openemr_postcalendar_events.pc_startTime,
openemr_postcalendar_events.pc_title,
openemr_postcalendar_events.pc_hometext
FROM
patient_data
left join
openemr_postcalendar_events on patient_data.pid = openemr_postcalendar_events.pc_pid
WHERE openemr_postcalendar_events.pc_eventDate=‘2022-10-05’
order by openemr_postcalendar_events.pc_startTime asc
;

#2

welcome @kmillernlbh , that query looks fine, sometimes when you post on the forum it makes 's appear as ` aka backtick which could be a problem if that’s what you’re using instead of single quotes

#3

Hi Stephen!

Thank you for your fast response. I went back and double checked to make sure there were only apostrophes, not backticks. They all looked okay. I guess there must be something else going on.

#4

Hi Stephen,

Thanks for that suggestion. We tried both apostrophes and backticks just to be thorough and received the same resulting error message. We’re trying to capture scheduled appointments for the coming day as a reference point for our admin staff. Is the openemr_postcalendar_events the right place to be looking for that data or is there another place you’d suggest?

#5

hi @pmalloy , yes, the query looks good except for the backtick that’s displayed. Are you in need of something in addition to the appointments report that can be found under Reports->Visits->Appointments?

#6

Thank you Stephen,

The info on the report Reports > Visits > Appointments is useful to us for this project, is there any way to export that into excel?

Thanks!
Kacie

#7

That would be a new feature however for a shortcut you can click and drag from the top left on the Provider column name and drag down and then automatically paste that into open office calc :slight_smile:

#8

Thanks, I will try that and see how it looks.

I am trying to create a query that pulls the same info as that report, which would be really helpful for our day-to-day use.

Kacie

#9

ok, try using the fetchAppointments function in library/appointments.inc.php then.

#10

With single quotes or double quotes it works very well

SELECT
patient_data.fname,
patient_data.lname,
openemr_postcalendar_events.pc_pid,
patient_data.DOB,
openemr_postcalendar_events.pc_eventDate,
openemr_postcalendar_events.pc_startTime,
openemr_postcalendar_events.pc_title,
openemr_postcalendar_events.pc_hometext
FROM
patient_data
left join
openemr_postcalendar_events on patient_data.pid = openemr_postcalendar_events.pc_pid
WHERE openemr_postcalendar_events.pc_eventDate = "2022-10-05"
order by openemr_postcalendar_events.pc_startTime asc;

Regards
Luis.

#11

Thank you, Stephen. I will try this as well!

#12

This is perfect!!! Thank you so much for your help!

Kacie

#13

Hi Luis,
Thank you again for helping us get this query to work. Would it be difficult to add to it, so that we can also pull which provider the client is scheduled to see?

Thanks!
Kacie

#14

Hello.
It can be something like this:

SELECT
p.fname, 
p.lname, 
pc.pc_pid, 
p.DOB, 
pc.pc_eventDate, 
pc.pc_startTime, 
pc.pc_title,
pc.pc_hometext,
CONCAT(u.fname, ' ', u.mname, ' ', u.lname) Provider
FROM
patient_data p
LEFT JOIN
openemr_postcalendar_events pc ON p.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
#15

Thank you so much, Luis! Thank worked perfectly! I really appreciate the help!

Kacie

2 Likes
#16

Hi Luis,

I have another fairly simple query that I am trying to make and I was wondering if you could help?

Thanks!
Kacie

#17

Yes, that’s fine. But if I can’t help you, maybe someone here can.

#18

Thank you so much for your reply! I am trying to write something similar to the query above, but I am going to remove the DOB, add fields for insurance, secondary insurance, location, program and active. Also wondering if it is possible to add and “end time” since there is already a field for the start time of the appointment.

Kacie

#19

what is location,program and active?

#20

Sorry @luisuriarte, I keep forgetting that some items are specific to us and may have different names. The location we use refers to whether the appointment is in office or telehealth, the program is what we use to differentiate between professional services class - private insurance and Medicaid appointments and active is actually a column we could leave off, I think it was a binary yes or no that we used from our old program to check whether the client was active status or not. That is the main reason I am having trouble writing queries or adding to them, the location names are very new to me and I don’t always know where to find them in reference to open EMR.

Thanks,
Kacie