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