Message-ID: <3F8593C2.4080809@yahoo.de>
Date: Thu, 09 Oct 2003 18:58:42 +0200
From: Janko Richter <jankorichter@yahoo.de>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.5b) Gecko/20030903 Thunderbird/0.2
MIME-Version: 1.0
To: colorob@tin.it, pgsql-sql@postgresql.org
Subject: Re: select
References: <OSehb.24081$e6.807127@twister2.libero.it>
In-Reply-To: <OSehb.24081$e6.807127@twister2.libero.it>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

roberto wrote:
> Dear friends,
> I have this table
> 
> table work{
> day date,
> hour integer,
> }
> 
> select * from work;
> 
> date         |     text
> -----------
> 1-1-2003    1
> 1-1-2003    1
> 2-1-2003    5
> 3-1-2003    10
> 5-1-2003    15
> 
> how can i obtain this?
> 
> date         |    text
> -----------
> 1-1-2003    2
> 2-1-2003    5
> 3-1-2003    10
> 4-1-2003    null
> 5-1-2003    15
> 6-1-2003    null
> 
> 

First , you need a sequence of days. Just create
a function like this:

CREATE OR REPLACE FUNCTION dateseq ( DATE , DATE )
RETURNS SETOF DATE
LANGUAGE 'plpgsql'
AS '
   DECLARE
     bdate ALIAS FOR $1 ;
     edate ALIAS FOR $2 ;
     cdate DATE ;
   BEGIN
     cdate := bdate;
     WHILE cdate <= edate LOOP
       RETURN NEXT cdate ;
       cdate := CAST ( cdate + interval ''1 day'' AS date );
     END LOOP;
     RETURN;
   END;
';

The function is like a table/view , where the fist function argument
is the start date , the second argument is the end date.

Now try :

SELECT ds.day, sum(w.hour)
FROM dateseq( '2003-1-1', '2003-1-6' ) AS ds (day)
LEFT JOIN work w ON ds.day=w.day
GROUP BY ds.day;

Regards, Janko
--
Janko Richter