Обсуждение: list of all months

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 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 /> 

On Mon, Mar 8, 2010 at 13:25, query <search2006@rediffmail.com> 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
mightnot have any data at all. With normal query, we can display days only if data exists.But I want to display rows
forall days in a month with blank data for non-existing day in database. 
>
> How can this be achieved ?

Say, you have a table like:

CREATE TABLE some_data (  date date NOT NULL,  some_value int
);

Now, You would like to print values
from March 2010, even if there is no
entry for some days in such a table.

We need to have a list of all the days
in March.  We can do it with a query:

SELECT date '2010-03-01' + n AS date FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n);

Sweet, we have dates, we just need a LEFT JOIN now:

SELECT date,      coalesce(value, 0) AS value  FROM some_data  RIGHT JOIN (    SELECT date '2010-03-01' + n AS date
FROM generate_series(0, date '2010-04-01' - date'2010-03-01' - 1) AS x(n)    ) AS dates USING (date); 

If you are running fairy recent PostgreSQL
it could be written even nicer:

WITH dates AS ( SELECT date '2010-03-01' + n AS date      FROM generate_series(0, date '2010-04-01' - date'2010-03-01'
-1) AS x(n) 
)
SELECT date,      coalesce(value, 0) AS value     FROM dates    LEFT JOIN some_data USING (date);


Two remarks:- it is fairy easy to create generate_series(date, date) function.  Give it a try - its fun! :)- coalesce
functionwill provide 0 in places where there is no  data row, or value is NULL. 

Best regards,   Dawid

It looks like a procedural problem.
I would solve it in plpgsql.

On 2010-03-08, query <search2006@rediffmail.com> wrote:
> --=_484d28810a276e7b5e461f0328ee205f
> Content-Transfer-Encoding: 7bit
> Content-Type: text/plain; charset="UTF-8"
>
> 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 ?
>  

an outer join to (select FIRST_DAY_OF_MONTH + generate_series(1,DAYS_IN_MONTH) -1 )
where the date columns match

FIRST_DAY_OF_MONTH and DAYS_IN_MONTH are to be replaced (by you) with the
apropriate expressions by some means, possibly date arithmetic.