dhis2-users team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

The use case of populating population data is one where I support using SQL
directly, and I don't think we will implement a function for this is in the
system. The below query is the one I used in Kenya to move population data
from one year to another, might be useful with some modification
(postgresql).

Lars



-- Moves population data from last year to this year

-- Replace first periodid (43668) with current year, replace second periodid
(21011) with last year, growth rate with desired rate (1.029), replace
dataset.name ('Population estimates') with population dataset name

delete from datavalue where periodid=43668 and dataelementid in (
select dataelementid from datasetmembers
join dataset using(datasetid)
where dataset.name='Population estimates' );

insert into
datavalue(dataelementid,periodid,sourceid,categoryoptioncomboid,value,storedby,lastupdated,comment,followup)
select dataelementid,43668 as
periodid,sourceid,categoryoptioncomboid,ceil(cast(value as double
precision)*1.029) as value,storedby,lastupdated,comment,followup
from datavalue
where periodid=21011 and dataelementid in (
select dataelementid from datasetmembers
join dataset using(datasetid)
where dataset.name='Population estimates' );