[SQL] need some magic with generate_series()
Hi I need a series of month numbers like 201212, 201301 MM 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 MM 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need some magic with generate_series()
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 : > Hi > I need a series of month numbers like 201212, 201301 MM 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 MM 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 ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need some magic with generate_series()
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 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 : > >> Hi >> I need a series of month numbers like 201212, 201301 MM 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 MM 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 ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need some magic with generate_series()
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, 'MM' )::integer fromgenerate_series ( $1, current_date, '1 month'::interval ) as m $BODY$ LANGUAGE sql STABLE; select project_id, month_series ( createdate ) fromprojects 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 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 : Hi I need a series of month numbers like 201212, 201301 MM 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 MM 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 ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [SQL] need some magic with generate_series()
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" To: "Filip Rembiałkowski" Cc: "jan zimmek" , 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, 'MM' )::integer fromgenerate_series ( $1, current_date, '1 month'::interval ) as m $BODY$ LANGUAGE sql STABLE; select project_id, month_series ( createdate ) fromprojects 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 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 : >> >>> Hi >>> I need a series of month numbers like 201212, 201301 MM 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 MM 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 ([email protected]) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] need some magic with generate_series()
The query should work for all projects in the projects table where everyone has a seperate startdate for the series. For the join I need ( project_id, month_nr ). When I tried I couldn't figure out how to feed the startdate into Filip's expression without using the function to encapsulate the generate_series(). The folowing doesn't work: select project_id, (select to_char ( m, 'MM' )::integer fromgenerate_series ( projects.createdate, current_date, '1 month'::interval ) as m ) fromprojects order by 1, 2; Am 23.01.2013 01:08, schrieb Alexander Gataric: I would create a common table expression with the series from Filip and left join to the table you need to report on. - Reply message - From: "Andreas" To: "Filip Rembiałkowski" Cc: "jan zimmek" , 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, 'MM' )::integer fromgenerate_series ( $1, current_date, '1 month'::interval ) as m $BODY$ LANGUAGE sql STABLE; select project_id, month_series ( createdate ) fromprojects 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 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 : >> >>> Hi >>> I need a series of month numbers like 201212, 201301 MM 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 MM 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 ([email protected]) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-sql >> >> >> -- >> Sent via pgsql-sql mailing list ([email protected]) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Re: [SQL] need some magic with generate_series()
Create a CTE with the project code and starting month of the project. Left join to month series CTE. Sent from my smartphone - Reply message - From: "Andreas" To: "Alexander Gataric" Cc: "Filip Rembiałkowski" , "jan zimmek" , Subject: [SQL] need some magic with generate_series() Date: Tue, Jan 22, 2013 7:00 pm The query should work for all projects in the projects table where everyone has a seperate startdate for the series. For the join I need ( project_id, month_nr ). When I tried I couldn't figure out how to feed the startdate into Filip's expression without using the function to encapsulate the generate_series(). The folowing doesn't work: select project_id, (select to_char ( m, 'MM' )::integer fromgenerate_series ( projects.createdate, current_date, '1 month'::interval ) as m ) fromprojects order by 1, 2; Am 23.01.2013 01:08, schrieb Alexander Gataric: > I would create a common table expression with the series from Filip > and > left join to the table you need to report on. > > - Reply message - > From: "Andreas" > To: "Filip Rembiałkowski" > Cc: "jan zimmek" , > 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, 'MM' )::integer >fromgenerate_series ( $1, current_date, '1 month'::interval ) > as m > > $BODY$ LANGUAGE sql STABLE; > > > select project_id, month_series ( createdate ) > fromprojects > 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 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 : > >> > >>> Hi > >>> I need a series of month numbers like 201212, 201301 MM 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 MM 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 ([email protected]) > >>> To make changes to your subscription: > >>> http://www.postgresql.org/mailpref/pgsql-sql > >> > >> > >> -- > >> Sent via pgsql-sql mailing list ([email protected]) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-sql > > > > -- > Sent via pgsql-sql mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
