Sample OpenMRS MySQL query to read data from database

I have a local implemetation of openmrs version 1.9.7 in a hospital in Kisumu, Kenya. Problem comes when I try to write queries to access the patient data collected from the database for data management purposes due to the complexity of the OpenMrs Database. I am also a little dusty with with sql since I have been off practice for a while but I need the data out asap. The query that I have currently is as below

  SELECT p.date_created as date_enrolled, pi.identifier,     pi.identifier_type     identifier_type ,
 pn.given_name,pn.middle_name, pn.family_name, p.person_id, p.gender, p.birthdate, p.death_date,  
ob.obs_datetime, cm.name as obs_type, CASE co.datatype_id    when '1' then ob.value_numeric   
when '2' then  (select name from concept_name where concept_id = ob.value_coded limit 1)   
 when '3' then ob.value_text    when '6' then ob.value_datetime    when '10' then ob.value_boolean    when '13' then ob.value_complex else "N/A" END AS obs_value, e.encounter_datetime 
FROM person p JOIN person_name pn ON p.person_id = pn.person_id
JOIN patient_identifier pi ON p.person_id = pi.patient_id 
JOIN patient_identifier_type pit ON pit.patient_identifier_type_id = pi.identifier_type 
JOIN obs ob ON p.person_id = ob.person_id JOIN encounter e ON e.encounter_id = ob.encounter_id 
JOIN concept_name cm ON ob.concept_id = cm.concept_id
JOIN concept co ON ob.concept_id = co.concept_id
JOIN concept_datatype cdt ON cdt.concept_datatype_id = co.datatype_id;

Is there an already existing query used that I can use as a starter and maybe modify to fit my needs? Or rather how best do you advice for me to go through this?

Thanks

Solution

You did quite well, considering all the tables involved. This is very complicated SQL because you are mixing header and detail data in a single row. You have 2 fairly large problems here if you want to write something in a single query: 1) concept_name has multiple locales (languages), when I suspect you only want one. Try something like locale='en' to reduce the number of rows. 2) the obs table is EAV (entity-attribute-value), plus as you discovered, different value fields correspond to different datatypes. To deal with this, take a look at MySql's GROUP_CONCAT function. Here is a rough shot at doing your query, though I removed some things to simplify and just to get it to work. It will give you an idea of how to use GROUP_CONCAT, but I punted on the datatype -> value CASE statement :

SELECT p.date_created as date_enrolled, 
pi.identifier, pi.identifier_type, pn.given_name,  
left(GROUP_CONCAT(ob.obs_datetime, cm.name SEPARATOR '\t'),60) as 'ItemDate|ItemName', 
e.encounter_datetime 
FROM person p JOIN person_name pn ON p.person_id = pn.person_id
JOIN patient_identifier pi ON p.person_id = pi.patient_id 
JOIN patient_identifier_type pit ON pit.patient_identifier_type_id = pi.identifier_type 
JOIN obs ob ON p.person_id = ob.person_id 
JOIN encounter e ON e.encounter_id = ob.encounter_id 
JOIN concept_name cm ON ob.concept_id = cm.concept_id
JOIN concept co ON ob.concept_id = co.concept_id
JOIN concept_datatype cdt ON cdt.concept_datatype_id = co.datatype_id 
where cm.locale = 'en'
group by 2 limit 10;

All that said, I don't think the OpenMRS folks would recommend that you keep trying this. Use their reporting tools instead.