SELECT `patient_identifier`.`identifier`, 
CONCAT(UPPER(`family_name`), ', ',`given_name`) as patient_name,
`admission_encounter`.`encounter_datetime` as admission_datetime, 
`discharge_encounter`.`encounter_datetime` as discharge_datetime, 
CONCAT(`admission_encounter`.`encounter_id`, ', ', `discharge_encounter`.`encounter_id`) as encounter_id, 
(SELECT CONCAT(`provider_name`.`family_name`, ', ', `provider_name`.`given_name`) as provider FROM `person_name` as `provider_name` WHERE `provider_name`.`preferred` = 1 AND `provider_name`.`voided` = 0 AND `provider_name`.`person_id` = (SELECT `person_id` FROM `provider` WHERE `provider_id` = (SELECT provider_id FROM encounter_provider WHERE voided = 0 AND encounter_id = `discharge_encounter`.`encounter_id` LIMIT 1) LIMIT 1 ) LIMIT 1) as provider,
(SELECT CONCAT_WS(', ',(SELECT `name` FROM `concept_name` WHERE `voided` = 0 AND `locale` IN ('en','fr') AND `concept_id` = `value_coded`  AND `concept_name_type` = 'FULLY_SPECIFIED' ORDER BY `locale` DESC LIMIT 1),(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 160531 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0))  FROM `obs` WHERE `concept_id` = 5219 AND `obs`.`encounter_id` = `admission_encounter`.`encounter_id` AND `voided` = 0) as chief_complaint,
(SELECT CONCAT_WS(', ',(SELECT name FROM concept_name WHERE voided = 0 AND locale in ('en','fr') AND concept_id=`value_coded` ORDER By locale DESC LIMIT 1),(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 162879 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0)) as concept_name  FROM `obs` WHERE `concept_id` = 1655 AND `obs`.`encounter_id` = `admission_encounter`.`encounter_id` AND `voided` = 0) as admission_reason,
(SELECT CONCAT_WS(', ', GROUP_CONCAT(UPPER((SELECT `name` FROM `concept_name` WHERE `voided` = 0 AND `locale` IN ('en','fr') AND `concept_id` = `diags`.`value_coded`  AND `concept_name_type` = 'FULLY_SPECIFIED' ORDER BY `locale` DESC LIMIT 1)) SEPARATOR ', '),(SELECT GROUP_CONCAT(UPPER(`diags`.`value_text`) SEPARATOR ', ') FROM `obs` as diags, `obs` as diag_type WHERE `diags`.`concept_id` = 161602 AND `diags`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `diag_type`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `diags`.`voided` = 0 AND `diag_type`.`voided` = 0 AND `diags`.`obs_group_id` = `diag_type`.`obs_group_id` AND `diag_type`.`concept_id` = 159394 AND `diag_type`.`value_coded` = 159393))  FROM `obs` as diags, `obs` as diag_type WHERE `diags`.`concept_id` = 1284 AND `diags`.`encounter_id` = `admission_encounter`.`encounter_id` AND `diag_type`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `diags`.`voided` = 0 AND `diag_type`.`voided` = 0 AND `diags`.`obs_group_id` = `diag_type`.`obs_group_id` AND `diag_type`.`concept_id` = 159394 AND `diag_type`.`value_coded` = 160250) as ddx_diags,
(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 163104 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as treatment_description,
(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 163046 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as cardio,
(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 159573 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as bedside_echo,
(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 160689 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as resp,
(SELECT CONCAT('Hémaglobine:',`value_numeric`,' g/dL - ',`comments`) as heme  FROM `obs` WHERE `concept_id` = 21 AND `encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as heme,
(SELECT CONCAT('Créatine kinase:',`value_numeric`,' IU/L') as heme FROM `obs` WHERE `concept_id` = 21 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as creatine,
(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 160983 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as renal,
(SELECT GROUP_CONCAT(`comments` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 1284 AND `value_coded` = 151673 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as infectious_disease,
(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 160951 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as gastro,
(SELECT GROUP_CONCAT(`comments` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 1284 AND `value_coded` = 142089 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as endocrine,
(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 163109 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as neuro,
(SELECT GROUP_CONCAT(`value_text` SEPARATOR ', ')  FROM `obs` WHERE `concept_id` = 163108 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as social,
(SELECT (SELECT `name` FROM `concept_name` WHERE `voided` = 0 AND `locale` IN ('en','fr') AND `concept_id` = `value_coded`  AND `concept_name_type` = 'FULLY_SPECIFIED' ORDER BY `locale` DESC LIMIT 1) as concept_name  FROM `obs` WHERE `concept_id` = 163105 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as disposition,
(SELECT GROUP_CONCAT(UPPER((SELECT `name` FROM `concept_name` WHERE `voided` = 0 AND `locale` IN ('en','fr') AND `concept_id` = `meds`.`value_coded`  AND `concept_name_type` = 'FULLY_SPECIFIED' ORDER BY `locale` DESC LIMIT 1)) SEPARATOR ', ')  FROM `obs` as meds, `obs` as stop_date WHERE `meds`.`concept_id` = 1282 AND `meds`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `stop_date`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `meds`.`voided` = 0 AND `stop_date`.`voided` = 0 AND `meds`.`obs_group_id` = `stop_date`.`obs_group_id` AND `stop_date`.`concept_id` = 1191 AND date(`discharge_encounter`.`encounter_datetime`) = `stop_date`.`value_datetime`) as meds_discontinued,
(SELECT GROUP_CONCAT(UPPER((SELECT name FROM concept_name WHERE voided = 0 AND locale in ('en','fr') AND concept_id=`value_coded` AND `concept_name_type` = 'FULLY_SPECIFIED' ORDER By locale DESC LIMIT 1)) SEPARATOR ', ') FROM `obs` WHERE `concept_id` = 162692 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as meds_on_discharge,
(SELECT `value_text`  FROM `obs` WHERE `concept_id` = 163106 AND `obs`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `voided` = 0) as instructions,
(SELECT CONCAT_WS(', ', GROUP_CONCAT(UPPER((SELECT `name` FROM `concept_name` WHERE `voided` = 0 AND `locale` IN ('en','fr') AND `concept_id` = `diags`.`value_coded`  AND `concept_name_type` = 'FULLY_SPECIFIED' ORDER BY `locale` DESC LIMIT 1)) SEPARATOR ', '),(SELECT GROUP_CONCAT(UPPER(`diags`.`value_text`) SEPARATOR ', ') FROM `obs` as diags, `obs` as diag_type WHERE `diags`.`concept_id` = 161602 AND `diags`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `diag_type`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `diags`.`voided` = 0 AND `diag_type`.`voided` = 0 AND `diags`.`obs_group_id` = `diag_type`.`obs_group_id` AND `diag_type`.`concept_id` = 159394 AND `diag_type`.`value_coded` = 160250))  FROM `obs` as diags, `obs` as diag_type WHERE `diags`.`concept_id` = 1284 AND `diags`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `diag_type`.`encounter_id` = `discharge_encounter`.`encounter_id` AND `diags`.`voided` = 0 AND `diag_type`.`voided` = 0 AND `diags`.`obs_group_id` = `diag_type`.`obs_group_id` AND `diag_type`.`concept_id` = 159394 AND `diag_type`.`value_coded` = 160250) as final_diags
FROM `person_name`,`patient_identifier`, `encounter` as discharge_encounter, `encounter` as admission_encounter WHERE `person_id` = :person AND `person_name`.`person_id`= `patient_identifier`.`patient_id` AND `patient_identifier`.`patient_id` = `discharge_encounter`.`patient_id` AND `patient_identifier`.`voided`=0 AND `person_name`.`voided`=0 AND `patient_identifier`.`preferred`=1 AND `person_name`.`preferred`=1 AND `patient_identifier`.`identifier_type`=3 AND `discharge_encounter`.`voided` = 0 AND `admission_encounter`.`encounter_type` = 4 AND `discharge_encounter`.`encounter_type` = 3 AND `admission_encounter`.`visit_id` = `discharge_encounter`.`visit_id` AND `admission_encounter`.`location_id` = `discharge_encounter`.`location_id` ORDER BY `discharge_encounter`.`encounter_datetime` DESC LIMIT 1