Closed kameshbhariya closed 3 weeks ago
Comments from Vivek -
it seems "Calculate the number of pregnant women with age <18 or >35.", "Calculate the number of pregnant women with height <140cm." are already calculated in report and the obs has the resultant value. I am not clear what the data is like, from this report.
dimensions are also wrong
from technical perspective, we should use crosstab or something like that instead of writing a CASE base aggregation.
F.43. tablefunc — functions that return tables (crosstab and others) https://www.postgresql.org
if for that we have to change the presentation of data a bit then we should do that
from technical perspective, we should use crosstab or something like that instead of writing a CASE base aggregation.
in crosstab also, we will have to list down all the concepts. so one way to do this is to make sector a filter
select count(x.id), trim(unnest(STRING_TO_ARRAY(x."High risk condition", ','))) as "High risk condition" from apfodishauat.individual_pregnancy_anc x where "High risk condition" is not null group by 2 order by 1;
we should avoid adding individual concept names in the report because they will keep breaking when we add new concept.
Not tested, tried writing this sample query -
set role apfodisha; WITH high_risk AS (SELECT addr."GP", TRIM(UNNEST(STRING_TO_ARRAY(x."High risk condition", ','))) AS "High risk condition" FROM apfodisha.individual_pregnancy_anc x JOIN apfodisha.address addr ON addr.id = x.address_id WHERE x."High risk condition" IS NOT NULL) -- Step 2: Aggregate the counts SELECT * FROM crosstab( 'SELECT "GP", high_risk_condition, COUNT(*) FROM ( SELECT addr."GP", TRIM(UNNEST(STRING_TO_ARRAY(x."High risk condition", '',''))) AS high_risk_condition FROM apfodisha.individual_pregnancy_anc x JOIN apfodisha.address addr ON addr.id = x.address_id WHERE x."High risk condition" IS NOT NULL ) sub GROUP BY "GP", high_risk_condition ORDER BY "GP", high_risk_condition', 'SELECT DISTINCT TRIM(UNNEST(STRING_TO_ARRAY(x."High risk condition", '',''))) FROM apfodisha.individual_pregnancy_anc x JOIN apfodisha.address addr ON addr.id = x.address_id WHERE x."High risk condition" IS NOT NULL ORDER BY 1' ) AS ct("GP" TEXT, "Geographically high risk" INT, "Height is less than 140cm" INT, "Condition3" INT, "Condition4" INT, "Condition5" INT, "Condition6" INT, "Condition7" INT, "Condition8" INT, "Condition9" INT, "Condition10" INT, "Condition11" INT, "Condition12" INT, "Condition13" INT, "Condition14" INT, "Condition15" INT, "Condition16" INT, "Condition17" INT, "Condition18" INT, "Condition19" INT, "Condition20" INT, "Condition21" INT, "Condition22" INT, "Condition23" INT, "Condition24" INT, "Condition25" INT);
As per final discussion we will use following query, update it to join with the address "Sector" and group by "Sector" and "High risk conditions" both.
We can have the first sector auto selected and a mandatory field