Skip to content

Instantly share code, notes, and snippets.

@petmongrels
Created January 11, 2021 11:48
Show Gist options
  • Save petmongrels/8ec5e82bfcb00c8c785e4876ad4f74e9 to your computer and use it in GitHub Desktop.
Save petmongrels/8ec5e82bfcb00c8c785e4876ad4f74e9 to your computer and use it in GitHub Desktop.
Multiple concept maps for a form
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