dhis2-users team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

You could install the function into the database through the postgres
backend.

Then you can include the function into select statements.  For example:

select organisationunit.name, getLatestPopulation(
organisationunit.organisationunitid).value from organisationunit;

On 24 September 2014 10:22, Adebusoye Anifalaje <busoye@xxxxxxxx> wrote:

> Hi Bob,
>
> I do not suppose you could use this statement in the sqlview since that's
> limited to Select statements. I recall Jason mentioned that the only way
> they are able to manage a WASH Program needing just the last current value
> is to dump the data and post updates though the web api. This seems to be
> way too much maintenance and would be better if one could use an sql
> statement like yours instead.
>
> Thanks
>
> Regards,
> Busoye
> On 24 Sep 2014, at 10:16, Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote:
>
> For what its worth the following is the sql I used to extract the latest
> value of a population dataelement.  I am sure it could be generalized and
> maybe implemented as a getLastValue method on a dataelement.
>
>  -------------------------------------------------------------------
> -- function returns most recent population estimate (and year) for
> -- an orgunitid
> -------------------------------------------------------------------
> CREATE OR REPLACE FUNCTION getLatestPopulation(orgunitid int, out value,
> out year)
> AS $$
> BEGIN
>   SELECT DISTINCT ON (sourceid)
>     value as population,
>     extract(year from startdate)
>   INTO
>     value, year
>   FROM datavalue
>     join period on period.periodid=datavalue.periodid
>     join organisationunit on
> organisationunit.organisationunitid=datavalue.sourceid
>     join dataelement on dataelement.dataelementid=datavalue.dataelementid
>   WHERE
>     organisationunit.organisationunitid = orgunitid AND
>     -- better to use domething like dataelement.code='POP'
>     -- better still to parameterize it
>     dataelement.name ='Population Total' AND
>     extract(year from startdate) <= extract('year' from current_timestamp)
>   ORDER BY sourceid,startdate DESC;
> END
>
> On 24 September 2014 09:55, Rodolfo Melia <rmelia@xxxxxxxxxxxx> wrote:
>
>> Hi Lars - I had a quick look in trunk. When editing a Data Element, I
>> still only see one Aggregation Operator. I was expecting to find two: one
>> for controlling the operator of aggregations across time, a second selector
>> for the aggregator across Org Units...
>>
>> In terms of priorities, below my list:
>> 1. Sum + Avg
>> 2. Count (You could have two type of counts: Different to 0 or Null, 2.
>> Different to Null
>> 3. LAST (across time only)
>> 4. Std Deviation
>> 5. everything else
>>
>> *Rodolfo Meliá*
>> *Principal  |  *rmelia@xxxxxxxxxxxx
>> Skype: rod.melia  |  +44 777 576 4090  |  +1 708 872 7636
>> www.knowming.com
>>
>> On Wed, Sep 24, 2014 at 8:47 AM, Lars Helge Øverland <larshelge@xxxxxxxxx
>> > wrote:
>>
>>> Hi there,
>>>
>>> we have implemented support for a "true" average aggregation operator in
>>> trunk now, which will average across both time and org unit dimensions.
>>>
>>> The existing average operator has been renamed to "Average (sum in
>>> organisation unit hierarchy)" in the UI.
>>>
>>> This will be part of 2.17.
>>>
>>> regards,
>>>
>>> Lars
>>>
>>>
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-devs
>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-users
> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>
>
>