Re: list of all months

-----BEGIN PGP SIGNED MESSAGE----- <br /> Hash: SHA1 <br />  <br /> One approach could be:<br /><br /> You build a
tablewith month information over which you are willing to<br /> show data from another table.<br /><br /> Then you just
crossjoin your data table and the data in your month table.<br /><br /> Here is some code I use for generating the
tablethat holds all the<br /> month I care about.<br /><br /><br /> DROP TABLE  IF EXISTS tmp_lookup_months;<br />
CREATETABLE tmp_lookup_months<br />     (month_year_str varchar(7) NOT NULL,<br />      first_day_of_month DATE NOT
NULL,<br/>      month INTEGER NOT NULL,<br />      year INTEGER NOT NULL,<br />      PRIMARY KEY (first_day_of_month,
year),<br/>      <br />      CONSTRAINT valid_date<br />         CHECK (<br />            (EXTRACT (YEAR FROM
first_day_of_month)::integer= year) AND<br />            (EXTRACT (MONTH FROM first_day_of_month)::integer = month)
AND<br/>            (EXTRACT (MONTH FROM first_day_of_month) > 0) AND<br />            (EXTRACT (MONTH FROM
first_day_of_month)< 13) AND          <br />            (EXTRACT (DAY FROM first_day_of_month) = 01) AND<br />
          (month_year_str) like (CASE WHEN month <= 9<br />             then<br />               cast (year::text
||'-0'||month::text  as char(7))   <br />       <br />             else<br />              cast (year::text||'-'|| 
month::text as char(7))      <br />            end)<br />            )       <br />     );<br /><br /><br /> INSERT
INTO tmp_lookup_months<br />     select month_year_str, first_day_of_month, month, year from   <br /> (<br />    
selectmonth, year,<br />            CASE WHEN month <= 9<br />             then<br />               cast (year::text
||'-0'||month::text  as char(7))   <br />       <br />             else<br />               cast (year::text||'-'|| 
month::text as char(7))    <br />       <br />            end as month_year_str,          <br />            cast
(year::text||'-'||month||'-1'as date) as<br /> first_day_of_month           <br />     from<br />    
generate_series(1990,2090) as year cross join<br />     generate_series(1,12) as month<br />     order by year,
month<br/> ) as t1;<br /><br /><br /> What is nice about this approach is that you can easily change the<br />
granularityof the time over which you are willing to show the info so<br /> you can create a second table with a
trimesterlist for example.<br /><br /><br /> Then say you have your data in a table called mydata.<br /><br /><br />
select   <br /><br />     ...<br /> from mydata<br /><br /> CROSS JOIN tmp_lookup_months as ym where(<br />     ym.year
>=$1 and  ym.year <= $2<br />    and my_intersection_function(start_date, end_date, ym.month, ym.year)>0<br />
)<br/> order by ...<br /><br /> So I'm assuming here that in the mydata table you have at least 4 columns:<br /> id,
start_date,end_date,  some_data.<br /> some_data field probably only makes sense over the start_date to<br /> end_date
interval.<br/> So in your select query you'll most likely need an aggregate function.<br /><br /><br /> If you could
provideda more complete description of what you are<br /> trying to achive I might be able to further help.<br /><br />
PetruGhita<br /><br /> On 08/03/2010 13:25, query wrote:<br /><span style="white-space: pre;">> Hi,<br /> > <br
/>> I want to display data for all days in a month even if no data <br /> > exists for that month. Some of the
daysin a month might not have <br /> > any data at all. With normal query, we can display days only if<br /> >
dataexists.But I want to display rows for all days in a month with<br /> > blank data for non-existing day in
database.<br/> > <br /> > How can this be achieved ?<br /> > <br /> > <a class="moz-txt-link-rfc2396E"
href="http://sigads.rediff.com/RealMedia/ads/click_nx.ads/www.rediffmail.com/signatureline.htm@Middle?"><http://sigads.rediff.com/RealMedia/ads/click_nx.ads/www.rediffmail.com/signatureline.htm@Middle?></a></span><br
/>-----BEGIN PGP SIGNATURE----- <br /> Version: GnuPG v1.4.9 (MingW32) <br /> Comment: Using GnuPG with Mozilla - <a
class="moz-txt-link-freetext"href="http://enigmail.mozdev.org/">http://enigmail.mozdev.org/</a><br />  <br />
iEYEARECAAYFAkueqZsACgkQt6IL6XzynQT+rgCguhFx6qzH3sgiti3O5zaqVQYS<br /> ra4Anjz1C8hS5YC6jRVD9coV6j1AxpPv <br /> =OoAd
<br/> -----END PGP SIGNATURE----- <br /><br />