dhis2-devs team mailing list archive

[Adebusoye Anifalaje <busoye@xxxxxxxx>]

Thank you Bob. I guess if Rodolfo’s dream doesn’t come true, then this would be the next best solution.

Cheers.

Busoye

On 24 Sep 2014, at 11:03, Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote:

> 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
> 
>