Date format Error on Query

Maybe you don’t understand your list well, but here are some ideas so you can create it. I hope it works for you.

For general appointment list one a date:

SELECT e.pc_eventDate, e.pc_endDate, e.pc_startTime, e.pc_endTime, e.pc_recurrfreq, e.pc_catid, e.pc_eid, e.pc_gid, e.pc_title, e.pc_hometext, 
		e.pc_apptstatus, CONCAT(p.fname, ' ', p.mname, ' ', p.lname) Patient, p.pid, p.pubpid,
        p.hipaa_allowsms, p.phone_home, p.phone_cell, p.hipaa_voice, p.hipaa_allowemail, p.email,
        u.fname AS ufname, u.mname AS umname, u.lname AS ulname, u.id AS uprovider_id,
        f.name, e.pc_room, e.pc_pid, t.id, t.eid, t.pid, 
		CONCAT(u.fname, ' ', u.mname, ' ', u.lname) Provider, t.encounter, q.pt_tracker_id, q.room
FROM openemr_postcalendar_events AS e
LEFT OUTER JOIN patient_tracker AS t ON t.pid = e.pc_pid AND t.apptdate = e.pc_eventDate AND t.appttime = e.pc_starttime 
AND t.eid = e.pc_eid 
LEFT OUTER JOIN patient_tracker_element AS q ON q.pt_tracker_id = t.id AND q.seq = t.lastseq
LEFT OUTER JOIN list_options AS s ON s.list_id = 'apptstat' AND s.option_id = q.status AND s.activity = 1 
LEFT OUTER JOIN facility AS f ON e.pc_facility = f.id
LEFT OUTER JOIN patient_data AS p ON p.pid = e.pc_pid
LEFT OUTER JOIN users AS u ON u.id = e.pc_aid
LEFT OUTER JOIN openemr_postcalendar_categories AS c ON c.pc_catid = e.pc_catid
WHERE e.pc_pid=p.pid	AND e.pc_eventDate='2022-10-10'
ORDER BY e.pc_eventDate, e.pc_startTime

For Insurances Companies for patients (PID patient = 1):

SELECT 
	CONCAT(id.subscriber_fname, ' ', id.subscriber_mname, ' ', id.subscriber_lname) Patient, 
	ic.name, id.type,
	itc.type,
	itc.claim_type
	FROM insurance_data id
	LEFT JOIN insurance_companies ic ON ic.id = id.provider
	LEFT JOIN insurance_type_codes itc ON ic.ins_type_code = itc.id
	WHERE id.pid = 1 AND id.provider <> ''
	ORDER BY id.type

A General appointment with location:

SELECT
p.fname, 
p.lname, 
pc.pc_pid, 
p.DOB, 
pc.pc_eventDate, 
pc.pc_startTime, 
pc.pc_title,
pc.pc_hometext,
(SELECT ic.name FROM insurance_companies AS ic LEFT JOIN insurance_data AS id ON id.type = "primary" WHERE p.pid = id.pid GROUP BY id.type) SS,
CONCAT
(u.fname, ' ', u.mname, ' ', u.lname) Provider,
(SELECT lo.title FROM list_options lo WHERE lo.option_id = pc.pc_room AND lo.list_id='patient_flow_board_rooms') Location
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;

For places, you can create a new one (for example Telehealth) in Administration/Forms/Lists → rooms and there add Telesalus, then when creating an appointment choose Telehealth.

If you want to place an Active status. You can modify demographic forms Administration/Forms/designs → choose demographics and enable a field, for example generic name (genericname2) must be type option list (List tbox) choose list YES/NO,

In the same way you can add another field with the Asset modification date.

1 Like

Thank you @luisuriarte! This is extremely helpful!!! I really appreciate you taking the time to help me learn my way around!

Kacie

1 Like

The last query, appointment with location, returns the error:

"Error in query (1055): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘nlbh_prod.ic.name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by "

Do you know how I can fix this? It may be something to do with our setup, but I am not sure what the error means.

Thanks!
Kacie

I got this all working! Just wanted to say thank you again to @luisuriarte for all of your help! I learned a lot from this!

Kacie

2 Likes