Created
January 11, 2021 11:48
-
-
Save petmongrels/8ec5e82bfcb00c8c785e4876ad4f74e9 to your computer and use it in GitHub Desktop.
Multiple concept maps for a form
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
WITH concepts_24 AS ( | |
SELECT hstore((array_agg(c2.uuid))::text[], (array_agg(c2.name))::text[]) AS map | |
FROM ((concept | |
JOIN concept_answer a ON ((concept.id = a.concept_id))) | |
JOIN concept c2 ON ((a.answer_concept_id = c2.id))) | |
WHERE ((concept.uuid)::text = ANY ((ARRAY['1a260251-1fcd-4f6c-b2e7-a0eb534d2cc2'::character varying, 'f1eb8ffc-f6b0-4089-9713-041041ea2c37'::character varying, '031ce79b-dc20-4137-b9e1-b5a7a7b97042'::character varying, 'd0f257d4-87a2-426d-b418-9403a23302c0'::character varying, '263938d1-822d-4f1e-9361-84032b2c9155'::character varying, '45beb4d7-7074-433c-8786-f9d6a19a5b34'::character varying])::text[])) | |
), concepts_23 AS ( | |
SELECT hstore((array_agg(c2.uuid))::text[], (array_agg(c2.name))::text[]) AS map | |
FROM ((concept | |
JOIN concept_answer a ON ((concept.id = a.concept_id))) | |
JOIN concept c2 ON ((a.answer_concept_id = c2.id))) | |
WHERE ((concept.uuid)::text = '7ee9aa23-94a0-4a32-820f-6eca1ac8d295'::text) | |
), concepts_22 AS ( | |
SELECT hstore((array_agg(c2.uuid))::text[], (array_agg(c2.name))::text[]) AS map | |
FROM ((concept | |
JOIN concept_answer a ON ((concept.id = a.concept_id))) | |
JOIN concept c2 ON ((a.answer_concept_id = c2.id))) | |
WHERE ((concept.uuid)::text = 'f1a9ede3-1b9b-4b1a-a8fb-6a3bb8f1cc19'::text) | |
) | |
SELECT individual.id AS individual_id, | |
op.name AS program_name, | |
op.program_id, | |
programenrolment.id, | |
programenrolment.uuid, | |
programenrolment.enrolment_date_time, | |
programenrolment.enrolment_location, | |
programenrolment.is_voided, | |
programenrolment.audit_id, | |
programenrolment.program_exit_date_time, | |
programenrolment.exit_location, | |
((programenrolment.observations ->> 'a9e95293-f004-41dd-8104-68c8b520f505'::text))::date AS "Date of admission at SNCU", | |
((programenrolment.observations ->> '4971e09f-8d97-4069-9094-cce4170e62a7'::text))::date AS "Date of discharge from SNCU", | |
((programenrolment.observations ->> '73b16ba7-50f5-40e0-b3df-8eb84f4dcf99'::text))::date AS "Follow up date for SNCU", | |
(get_coded_string_value((programenrolment.observations -> '7ee9aa23-94a0-4a32-820f-6eca1ac8d295'::text), concepts_23.map))::text AS "Diagnosis at the time of discharge", | |
((programenrolment.observations ->> 'c82cd1c8-d0a9-4237-b791-8d64e52b6c4a'::text))::numeric AS "Birth Weight", | |
((programenrolment.observations ->> 'a881b2a4-a63f-47d1-b60f-915a95fab40f'::text))::numeric AS "Weight at the time of Discharge", | |
((programenrolment.observations ->> '66061ac7-49af-4162-913e-9af9ad97e182'::text))::numeric AS "Age at the time of discharge", | |
(programenrolment.observations ->> '795b13cc-d1db-4ad7-9bb8-ec50854253bd'::text) AS "Village where child went after discharge?", | |
(get_coded_string_value((programenrolment.observations -> '031ce79b-dc20-4137-b9e1-b5a7a7b97042'::text), concepts_24.map))::text AS "Discharge criteria", | |
(get_coded_string_value((programenrolment.observations -> 'f1eb8ffc-f6b0-4089-9713-041041ea2c37'::text), concepts_24.map))::text AS "Did child cry immedately after birth", | |
(get_coded_string_value((programenrolment.observations -> '1a260251-1fcd-4f6c-b2e7-a0eb534d2cc2'::text), concepts_24.map))::text AS "At the time of discharge, was the child able to take breast fee", | |
(get_coded_string_value((programenrolment.observations -> '45beb4d7-7074-433c-8786-f9d6a19a5b34'::text), concepts_24.map))::text AS "ROP screening done in case of preterm child?", | |
(get_coded_string_value((programenrolment.observations -> '263938d1-822d-4f1e-9361-84032b2c9155'::text), concepts_24.map))::text AS "If yes, is more treatment required for the eye?", | |
(get_coded_string_value((programenrolment.observations -> 'd0f257d4-87a2-426d-b418-9403a23302c0'::text), concepts_24.map))::text AS "Hearing test done at hospital?" | |
FROM ((((((program_enrolment programenrolment | |
CROSS JOIN concepts_24) | |
CROSS JOIN concepts_23) | |
CROSS JOIN concepts_22) | |
LEFT JOIN operational_program op ON ((op.program_id = programenrolment.program_id))) | |
LEFT JOIN individual individual ON ((programenrolment.individual_id = individual.id))) | |
LEFT JOIN operational_subject_type ost ON ((ost.subject_type_id = individual.subject_type_id))) | |
WHERE (((op.uuid)::text = '70e4db05-c79a-406c-89be-54f8d77f643a'::text) AND ((ost.uuid)::text = '40c1cf14-c902-4013-a61f-94467d5c904f'::text)); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment