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
