Re: list of all months

-----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(dot)htm(at)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-----