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.picker...@gmail.com> 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.row...@gmail.com> > 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.picker...@gmail.com> 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.row...@gmail.com> >>> 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@lists.launchpad.net >>>> Unsubscribe : https://launchpad.net/~dhis2-devs >>>> More help : https://help.launchpad.net/ListHelp >>>> >>>> >>> >>> >>> -- >>> Jason P. Pickering >>> email: jason.p.picker...@gmail.com >>> 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.picker...@gmail.com > tel:+46764147049 > -- *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@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp