The primary objective of this documentation is to remove the dependency of the Calcutta Kids organization's implementation from Org 1.
- Task:
set role dfad_trial; | |
select gender.name Gender, | |
EXTRACT(YEAR FROM AGE(ind.date_of_birth)) Age, | |
enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d' Hemoglobin, | |
enc.observations ->> 'd304f306-deca-4418-a9a5-27b04e083623' "Anaemic status", | |
case | |
when EXTRACT(YEAR FROM AGE(ind.date_of_birth)) < 12 | |
AND (enc.observations ->> '0f283bcc-fa4c-4d98-93fd-cbf1729b770d')::numeric >= 11 |
WITH disorder_data AS (SELECT 'Mental Illness' AS disorder_name, | |
individual.id AS individual_id, | |
EXTRACT(YEAR FROM age(date_of_birth))::INTEGER AS age, | |
gender AS gender, | |
enrolment.id AS enrolment_id, | |
"Reason for exit" | |
FROM jsscpuat_new.individual_mental_illness enrolment | |
RIGHT JOIN jsscpuat_new.individual ON individual.id = enrolment.individual_id | |
LEFT JOIN jsscpuat_new.individual_mental_illness_exit | |
ON individual.id = individual_mental_illness_exit.individual_id |
set role aptuat; | |
select name, uuid | |
from concept | |
where uuid in ('dd0bc18d-0661-45d5-84cc-45b1e0d4fba6', 'c2698021-44dc-4b43-a877-3801d4a8c20b', | |
'5e37269c-1b30-4766-b48a-893b77fd9a1a', 'ad12fc9b-cbdf-4ba4-ab27-e03e89a9af23', | |
'9a2bf64b-41b1-485e-a6dd-ceb1b75a8397', 'b7d832c6-3e84-43df-b2d2-ddbeb02f5739'); | |
-- Select the count of rows with "Grade" = '6' and "Type of School" = 'Primary' before the update | |
-- Secondary Grade 6 count should be 0 |
-- Select the data before the update | |
select observations ->> '2966afcc-2c07-44cf-8711-3fc23f52a6b5', | |
observations ->> '526b0d5d-51cc-4004-8c12-7a6c71c6c516', | |
observations ->> 'a043fea3-1658-4b5e-becd-ee55ab305a03', | |
observations ->> '1157c7c8-9f27-410d-9115-ef36191fba06' | |
from individual | |
where individual.organisation_id = (select id from organisation where name = 'Goonj Revamp'); | |
-- Remove off all the questions from the "Number of Participants" question group. | |
update individual |
-- Child Death | |
SELECT COALESCE(a.title, 'Total') AS block, | |
COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days <= 28) AS neonatal_death, | |
COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days > 28 AND age_days <= 365) AS infant_death, | |
COUNT(*) FILTER (WHERE reason_for_exit = 'cbb0969c-c7fe-4ce4-b8a2-670c4e3c5039' AND age_days > 365) AS child_death | |
FROM address_level a | |
LEFT JOIN address_level_type at ON at.id = a.type_id | |
LEFT JOIN ( | |
SELECT |