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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 | *[email protected] >> 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 <[email protected] >> > 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 : [email protected] >> Unsubscribe : https://launchpad.net/~dhis2-devs >> More help : https://help.launchpad.net/ListHelp >> >> > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-users > Post to : [email protected] > Unsubscribe : https://launchpad.net/~dhis2-users > More help : https://help.launchpad.net/ListHelp > > >
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
