Re: select

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