I would create a common table expression with the series from Filip and left join to the table you need to report on.
Sent from my smartphone ----- Reply message ----- From: "Andreas" <maps...@gmx.net> To: "Filip RembiaĆkowski" <plk.zu...@gmail.com> Cc: "jan zimmek" <jan.zim...@web.de>, <pgsql-sql@postgresql.org> Subject: [SQL] need some magic with generate_series() Date: Tue, Jan 22, 2013 4:49 pm Thanks Filip, with your help I came a step further. :) Could I do the folowing without using a function? CREATE OR REPLACE FUNCTION month_series ( date ) RETURNS table ( monthnr integer ) AS $BODY$ select to_char ( m, 'YYYYMM' )::integer from generate_series ( $1, current_date, '1 month'::interval ) as m $BODY$ LANGUAGE sql STABLE; select project_id, month_series ( createdate ) from projects order by 1, 2; Am 22.01.2013 22:52, schrieb Filip RembiaĆkowski: > or even > > select m from generate_series( '20121101'::date, '20130101'::date, '1 > month'::interval) m; > > > > On Tue, Jan 22, 2013 at 3:49 PM, jan zimmek <jan.zim...@web.de> wrote: >> hi andreas, >> >> this might give you an idea how to generate series of dates (or other >> datatypes): >> >> select g, (current_date + (g||' month')::interval)::date from >> generate_series(1,12) g; >> >> regards >> jan >> >> Am 22.01.2013 um 22:41 schrieb Andreas <maps...@gmx.net>: >> >>> Hi >>> I need a series of month numbers like 201212, 201301 YYYYMM to join other >>> sources against it. >>> >>> I've got a table that describes projects: >>> projects ( id INT, project TEXT, startdate DATE ) >>> >>> and some others that log events >>> events( project_id INT, createdate DATE, ...) >>> >>> to show some statistics I have to count events and present it as a view >>> with the project name and the month as YYYYMM starting with startdate of >>> the projects. >>> >>> My problem is that there probaply arent any events in a month but I still >>> need this line in the output. >>> So somehow I need to have a select that generates: >>> >>> project 7,201211 >>> project 7,201212 >>> project 7,201301 >>> >>> It'd be utterly cool to get this for every project in the projects table >>> with one select. >>> >>> Is there hope? >>> >>> >>> -- >>> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> -- >> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql