SELECT
pi.identifier,
ppa.value_reference,
cn.name,
o.value_datetime,
o.concept_id,
o.value_coded
FROM episode_encounter ee
JOIN obs o ON o.encounter_id = ee.encounter_id
AND o.concept_id = 1445
AND o.value_coded = 473
JOIN concept_name cn ON cn.concept_id = o.concept_id
AND cn.concept_name_type = 'FULLY_SPECIFIED'
JOIN episode_patient_program epp ON epp.episode_id = ee.episode_id
JOIN patient_program pp ON pp.patient_program_id = epp.patient_program_id
JOIN patient_program_attribute ppa ON ppa.patient_program_id = epp.patient_program_id
AND ppa.voided = 0
JOIN patient_identifier pi ON pi.patient_id = pp.patient_id
JOIN person p ON p.person_id = pi.patient_id; |