dhis2-devs team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

I do agree its not the final solution.  Just thought I'd share as a useful
hack/workaround for some problems.  I am no sql wizard, so was quite
pleased with myself that this worked :-)

On 24 September 2014 11:26, Rodolfo Melia <rmelia@xxxxxxxxxxxx> wrote:

> The problem with that approach is that the function is not available on
> Pivot Tables or Event Visualizer, where people need it.. Anyway, nice to
> know that you can call it via the SQL views.
>
> *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 11:03 AM, 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
>>>
>>>
>>>
>>
>