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 <knu...@gmail.com> 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.picker...@gmail.com> 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.row...@gmail.com> >> 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.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 >>> >> >> >> >> -- >> Jason P. Pickering >> email: jason.p.picker...@gmail.com >> tel:+46764147049 >> >> _______________________________________________ >> 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 >> >> > > > -- > Knut Staring > Dept. of Informatics, University of Oslo > Norway: +4791880522 > Skype: knutstar > http://dhis2.org > -- Jason P. Pickering email: jason.p.picker...@gmail.com tel:+46764147049
_______________________________________________ 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