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 <bobjolli...@gmail.com> 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 <bus...@hisp.org> 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 <bobjolli...@gmail.com> 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 <rme...@knowming.com> 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 | rme...@knowming.com
>> 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 <larshe...@gmail.com>
>> 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@lists.launchpad.net
>> Unsubscribe : https://launchpad.net/~dhis2-devs
>> More help : https://help.launchpad.net/ListHelp
>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-users
>> Post to : dhis2-us...@lists.launchpad.net
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help : https://help.launchpad.net/ListHelp
>
>
_______________________________________________
Mailing list: https://launchpad.net/~dhis2-devs
Post to : dhis2-devs@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-devs
More help : https://help.launchpad.net/ListHelp
