Hi all, I'm trying to enumerate a list of months between a date in the past and now and display a value from an existing table if there is one for the date or NULL if there isn't.
I'm using this SQL to generate the months: select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date as tally_mon from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as tally_day [[ produces ]] tally_mon ------------ 2011-06-01 2011-07-01 2011-08-01 2011-09-01 2011-10-01 (5 rows) and I am trying to use it as the left hand side of a left join against my data table: select tally_table. tally_mon, met.datum from ( select distinct date_trunc( 'month', '2011-06-01'::date + tally_day )::date as tally_mon from generate_series( 0, ( select current_date - '2011-06-01'::date ) ) as tally_day ) as tally_table full outer join my_existing_table as met on( tally_mon = met.month ) where met.series = 1; -- ** See SETUP below ** This produces rows only for those that exist in my_existing_table and no left join output which I'd expect for the rest of the tally_table rows. What am I missing? Many thanks, Joel SETUP: create temp table my_existing_table ( month date not null, series int not null, datum int not null ); insert into my_existing_table values ( '2011-08-01', 1, 5 ), ( '2011-10-01', 1, 4 ); -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
