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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
