dhis2-devs team mailing list archive

[Jason Pickering <jason.p.pickering@xxxxxxxxx>]

Yeah, of course

SELECT startdate::date,(startdate::date + '1 month'::interval - '1
day'::interval)::date as enddate from
(SELECT * FROM generate_series('2015-01-01'::date,'2015-12-31'::date, '1
month') as startdate) as foo

:)



On Mon, Sep 28, 2015 at 3:02 PM, Knut Staring <knutst@xxxxxxxxx> wrote:

> Nice, Jason. But did you mean enddate at the end of the first line (just
> before "from")?
>
> On Mon, Sep 28, 2015 at 2:53 PM, Jason Pickering <
> jason.p.pickering@xxxxxxxxx> wrote:
>
>> If you are using Postgres, you could always simply generate the periods
>> your self with something like..
>>
>> SELECT startdate::date,(startdate::date + '1 month'::interval - '1
>> day'::interval)::date as startdate from
>> (SELECT * FROM generate_series('2015-01-01'::date,'2015-12-31'::date, '1
>> month') as startdate) as foo
>>
>>
>>
>> On Mon, Sep 28, 2015 at 2:43 PM, Greg Rowles <greg.rowles@xxxxxxxxx>
>> wrote:
>>
>>> I guess there's no short way of explaining this.
>>>
>>> I'm using a SQL query (that allows for variables) to create a count of
>>> orgunits (coming from a specific ou-level) per orgunit groupset. I've set
>>> up an example on the dhis2 demo site here:
>>>
>>> https://apps.dhis2.org/demo/api/sqlViews/mLsbp3ds7pF/data?var=idlevel:idlevel4&var=parentidlevel:uidlevel1&var=ougroupset:Facility%20Type&var=ou:ImspTQPwCqd&var=pe:2015
>>>
>>> This lists and counts the orgunit-groups inside groupset [Facility Type]
>>> for the year 2015 summarised up to the national level
>>> (parentidlevel:uidlevel1) under region ou:ImspTQPwCqd. It shows a break
>>> down across 3 columns
>>>  - open (those in operation in the year),
>>>  - created (opened in the year)
>>>  - closed (in the year)
>>>
>>> The raw query looks like this:
>>>
>>> SELECT yperiod as pe, ouuid as ou, OUgroup, Sum(Open) as Open,
>>> Sum(OpenedNew) as Created, Sum(Closing) as Closed FROM ( SELECT
>>> "P".periodid, "P".startdate, "P".enddate, to_char("P".startdate, 'YYYY') as
>>> yPeriod, "P".periodtypeid, "ST".organisationunitid, "${parentidlevel}" as
>>> ouuid, "ST"."${ougroupset}" as OUgroup, "O".uid, "O".name,
>>> CAST("O".openingdate as DATE) as ValidFrom, CAST(coalesce("O".closeddate,
>>> '9999-12-31') as DATE) as ValidTo, ( SELECT CASE WHEN
>>> CAST(coalesce("O".closeddate, '9999-12-31') as DATE) > CAST("P".startdate
>>> as DATE) THEN 1 ELSE 0 END AS Result ) as Open, ( SELECT CASE WHEN
>>> CAST("O".openingdate as DATE) >= CAST("P".startdate as DATE) AND
>>> CAST("O".openingdate as DATE) <= CAST("P".enddate as DATE) THEN 1 ELSE 0
>>> END AS Result ) as OpenedNew, ( SELECT CASE WHEN
>>> CAST(coalesce("O".closeddate, '9999-12-31') as DATE) >= CAST("P".startdate
>>> as DATE) AND CAST(coalesce("O".closeddate, '9999-12-31') as DATE) <=
>>> CAST("P".enddate as DATE) THEN 1 ELSE 0 END AS Result ) as Closing FROM
>>> public.period "P", public._organisationunitgroupsetstructure "ST",
>>> public.organisationunit "O", public._orgunitstructure "S" WHERE
>>> "O".organisationunitid = "ST".organisationunitid AND "O".organisationunitid
>>> = "${idlevel}" AND "P".periodtypeid = 8 and CAST("P".startdate as DATE) <=
>>> current_date ) as foo WHERE ouuid = '${ou}' AND yperiod = '${pe}' GROUP BY
>>> yperiod, ouuid, OUgroup ORDER BY yperiod, OUgroup;
>>>
>>>
>>> Period table is included to simplify the open and close date criteria
>>> (AND "P".periodtypeid = 8) but these dates could probably be parsed as
>>> additional var options. Will add dummy data and then delete...
>>>
>>> Regards,
>>> Greg
>>>
>>>
>>>
>>> On Mon, Sep 28, 2015 at 2:22 PM, Jason Pickering <
>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>
>>>> I am not really sure why this would be needed. Could you explain more
>>>> why you need this?
>>>>
>>>> The period formats are well documented (
>>>> https://www.dhis2.org/doc/snapshot/en/developer/html/ch01s04.html), so
>>>> there is no need to have them stored by the server. The client can simply
>>>> create the periods as they need them. If they already exist, nothing will
>>>> happen, otherwise, they will by dynamically created when data is imported.
>>>>
>>>> Regards,
>>>> Jason
>>>>
>>>>
>>>> On Mon, Sep 28, 2015 at 2:19 PM, Greg Rowles <greg.rowles@xxxxxxxxx>
>>>> wrote:
>>>>
>>>>> Hi Jason
>>>>>
>>>>> Yeah, its for a "non-standard" DHIS2 instance. Our national data
>>>>> dictionary doesn't host actual data only master meta-data. Any suggestions?
>>>>> I don't see any options for periods under meta-data export either...
>>>>>
>>>>> Greg
>>>>>
>>>>>
>>>>> On Mon, Sep 28, 2015 at 2:17 PM, Jason Pickering <
>>>>> jason.p.pickering@xxxxxxxxx> wrote:
>>>>>
>>>>>> Hi Greg,
>>>>>>
>>>>>> There is no need to do this, as they will be added when data actually
>>>>>> exists for that period.
>>>>>>
>>>>>> I suppose you could add a dummy record and delete it, and that would
>>>>>> have the same affect as creating the period.
>>>>>>
>>>>>> Regards,
>>>>>> Jason
>>>>>>
>>>>>>
>>>>>> On Mon, Sep 28, 2015 at 2:10 PM, Greg Rowles <greg.rowles@xxxxxxxxx>
>>>>>> wrote:
>>>>>>
>>>>>>> Hi Devs
>>>>>>>
>>>>>>> Is there an API call to add missing period records? E.g. we have no
>>>>>>> yearly (periodtypeid: 8) records in an instance which has restricted
>>>>>>> access. Only API calls are supported...
>>>>>>>
>>>>>>> Regards,
>>>>>>> Greg
>>>>>>>
>>>>>>> --
>>>>>>> *Health Information Systems Program - South Africa*
>>>>>>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **-
>>>>>>> - - - - *
>>>>>>> Mobile  :    073 246 2992
>>>>>>> Landline:   021 554 3130
>>>>>>> Fax:          086 733 8432
>>>>>>> Skype:      gregory_rowles
>>>>>>>
>>>>>>> _______________________________________________
>>>>>>> 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
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Jason P. Pickering
>>>>>> email: jason.p.pickering@xxxxxxxxx
>>>>>> tel:+46764147049
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> *Health Information Systems Program - South Africa*
>>>>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- -
>>>>> - - - *
>>>>> Mobile  :    073 246 2992
>>>>> Landline:   021 554 3130
>>>>> Fax:          086 733 8432
>>>>> Skype:      gregory_rowles
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Jason P. Pickering
>>>> email: jason.p.pickering@xxxxxxxxx
>>>> tel:+46764147049
>>>>
>>>
>>>
>>>
>>> --
>>> *Health Information Systems Program - South Africa*
>>> *- - - - - - - **- - - - - - - **- - - - - - - **- - - - - - - **- - -
>>> - - *
>>> Mobile  :    073 246 2992
>>> Landline:   021 554 3130
>>> Fax:          086 733 8432
>>> Skype:      gregory_rowles
>>>
>>
>>
>>
>> --
>> Jason P. Pickering
>> email: jason.p.pickering@xxxxxxxxx
>> tel:+46764147049
>>
>> _______________________________________________
>> 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
>>
>>
>
>
> --
> Knut Staring
> Dept. of Informatics, University of Oslo
> Norway: +4791880522
> Skype: knutstar
> http://dhis2.org
>



-- 
Jason P. Pickering
email: jason.p.pickering@xxxxxxxxx
tel:+46764147049