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 < [email protected]> 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 <[email protected]> > 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 < >> [email protected]> 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 <[email protected]> >>> 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 < >>>> [email protected]> 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 <[email protected]> >>>>> 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 : [email protected] >>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs >>>>>> More help : https://help.launchpad.net/ListHelp >>>>>> >>>>>> >>>>> >>>>> >>>>> -- >>>>> Jason P. Pickering >>>>> email: [email protected] >>>>> 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: [email protected] >>> 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: [email protected] > tel:+46764147049 > > _______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : [email protected] > 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
_______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
