Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 52C3C643CE9 for ; Mon, 15 Mar 2010 18:43:22 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 32632-04 for ; Mon, 15 Mar 2010 21:43:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.akn.me (mail.akn.me [188.40.171.129]) by mail.postgresql.org (Postfix) with ESMTP id 5595C643C58 for ; Mon, 15 Mar 2010 18:43:11 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by mail.akn.me (Postfix) with ESMTP id B87354881518; Mon, 15 Mar 2010 22:05:34 +0100 (CET) X-Virus-Scanned: amavisd-new at akn.me Received: from mail.akn.me ([127.0.0.1]) by localhost (mail.akn.me [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 0J6jz1bGYSF6; Mon, 15 Mar 2010 22:05:30 +0100 (CET) Received: from [127.0.0.1] (213.37.99.4.dyn.user.ono.com [213.37.99.4]) by mail.akn.me (Postfix) with ESMTP id A19FA4881517; Mon, 15 Mar 2010 22:05:29 +0100 (CET) Message-ID: <4B9EA99B.50301@venaver.info> Date: Mon, 15 Mar 2010 22:41:47 +0100 From: Petru Ghita User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.8) Gecko/20100227 Thunderbird/3.0.3 MIME-Version: 1.0 To: query Subject: Re: list of all months References: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com> In-Reply-To: <20100308122523.50565.qmail@f5mail-237-209.rediffmail.com> X-Enigmail-Version: 1.0.1 Content-Type: multipart/alternative; boundary="------------080103020502000105020600" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/93 X-Sequence-Number: 33800 This is a multi-part message in MIME format. --------------080103020502000105020600 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 One approach could be: You build a table with month information over which you are willing to show data from another table. Then you just cross join your data table and the data in your month table. Here is some code I use for generating the table that holds all the month I care about. DROP TABLE IF EXISTS tmp_lookup_months; CREATE TABLE tmp_lookup_months (month_year_str varchar(7) NOT NULL, first_day_of_month DATE NOT NULL, month INTEGER NOT NULL, year INTEGER NOT NULL, PRIMARY KEY (first_day_of_month, year), CONSTRAINT valid_date CHECK ( (EXTRACT (YEAR FROM first_day_of_month)::integer = year) AND (EXTRACT (MONTH FROM first_day_of_month)::integer = month) AND (EXTRACT (MONTH FROM first_day_of_month) > 0) AND (EXTRACT (MONTH FROM first_day_of_month) < 13) AND (EXTRACT (DAY FROM first_day_of_month) = 01) AND (month_year_str) like (CASE WHEN month <= 9 then cast (year::text ||'-0'|| month::text as char(7)) else cast (year::text||'-'|| month::text as char(7)) end) ) ); INSERT INTO tmp_lookup_months select month_year_str, first_day_of_month, month, year from ( select month, year, CASE WHEN month <= 9 then cast (year::text ||'-0'|| month::text as char(7)) else cast (year::text||'-'|| month::text as char(7)) end as month_year_str, cast (year::text||'-'||month||'-1' as date) as first_day_of_month from generate_series(1990, 2090) as year cross join generate_series(1,12) as month order by year, month ) as t1; What is nice about this approach is that you can easily change the granularity of the time over which you are willing to show the info so you can create a second table with a trimester list for example. Then say you have your data in a table called mydata. select ... from mydata CROSS JOIN tmp_lookup_months as ym where( ym.year >= $1 and ym.year <= $2 and my_intersection_function(start_date, end_date, ym.month, ym.year)>0 ) order by ... So I'm assuming here that in the mydata table you have at least 4 columns: id, start_date, end_date, some_data. some_data field probably only makes sense over the start_date to end_date interval. So in your select query you'll most likely need an aggregate function. If you could provided a more complete description of what you are trying to achive I might be able to further help. Petru Ghita On 08/03/2010 13:25, query wrote: > Hi, > > I want to display data for all days in a month even if no data > exists for that month. Some of the days in a month might not have > any data at all. With normal query, we can display days only if > data exists.But I want to display rows for all days in a month with > blank data for non-existing day in database. > > How can this be achieved ? > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkueqZsACgkQt6IL6XzynQT+rgCguhFx6qzH3sgiti3O5zaqVQYS ra4Anjz1C8hS5YC6jRVD9coV6j1AxpPv =OoAd -----END PGP SIGNATURE----- --------------080103020502000105020600 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit -----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
 
One approach could be:

You build a table with month information over which you are willing to
show data from another table.

Then you just cross join your data table and the data in your month table.

Here is some code I use for generating the table that holds all the
month I care about.


DROP TABLE  IF EXISTS tmp_lookup_months;
CREATE TABLE tmp_lookup_months
    (month_year_str varchar(7) NOT NULL,
     first_day_of_month DATE NOT NULL,
     month INTEGER NOT NULL,
     year INTEGER NOT NULL,
     PRIMARY KEY (first_day_of_month, year),
     
     CONSTRAINT valid_date
        CHECK (
           (EXTRACT (YEAR FROM first_day_of_month)::integer = year) AND
           (EXTRACT (MONTH FROM first_day_of_month)::integer = month) AND
           (EXTRACT (MONTH FROM first_day_of_month) > 0) AND
           (EXTRACT (MONTH FROM first_day_of_month) < 13) AND          
           (EXTRACT (DAY FROM first_day_of_month) = 01) AND
           (month_year_str) like (CASE WHEN month <= 9
            then
              cast (year::text ||'-0'|| month::text  as char(7))   
      
            else
             cast (year::text||'-'||  month::text  as char(7))      
           end)
           )       
    );


INSERT INTO  tmp_lookup_months
    select month_year_str, first_day_of_month, month, year from   
(
    select month, year,
           CASE WHEN month <= 9
            then
              cast (year::text ||'-0'|| month::text  as char(7))   
      
            else
              cast (year::text||'-'||  month::text  as char(7))    
      
           end as month_year_str,          
           cast (year::text||'-'||month||'-1' as date) as
first_day_of_month           
    from
    generate_series(1990, 2090) as year cross join
    generate_series(1,12) as month
    order by year, month
) as t1;


What is nice about this approach is that you can easily change the
granularity of the time over which you are willing to show the info so
you can create a second table with a trimester list for example.


Then say you have your data in a table called mydata.


select    

    ...
from mydata

CROSS JOIN tmp_lookup_months as ym where(
    ym.year >= $1 and  ym.year <= $2
   and my_intersection_function(start_date, end_date, ym.month, ym.year)>0
)
order by ...

So I'm assuming here that in the mydata table you have at least 4 columns:
id, start_date, end_date,  some_data.
some_data field probably only makes sense over the start_date to
end_date interval.
So in your select query you'll most likely need an aggregate function.


If you could provided a more complete description of what you are
trying to achive I might be able to further help.

Petru Ghita

On 08/03/2010 13:25, query wrote:
> Hi,
>
> I want to display data for all days in a month even if no data
> exists for that month. Some of the days in a month might not have
> any data at all. With normal query, we can display days only if
> data exists.But I want to display rows for all days in a month with
> blank data for non-existing day in database.
>
> How can this be achieved ?
>
> <http://sigads.rediff.com/RealMedia/ads/click_nx.ads/www.rediffmail.com/signatureline.htm@Middle?>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
 
iEYEARECAAYFAkueqZsACgkQt6IL6XzynQT+rgCguhFx6qzH3sgiti3O5zaqVQYS
ra4Anjz1C8hS5YC6jRVD9coV6j1AxpPv
=OoAd
-----END PGP SIGNATURE-----

--------------080103020502000105020600--