Re: Problem with SELECT

am Mon, dem 08.10.2007, um 3:28:04 -0700 mailte marwis1978 folgendes:
> I have a following table
> -----+--------
> day | quantity
> -----+--------
>
> where day is a date and quantity is an integer value. Now I need to
> make a SELECT statement on this table which returns me a full
> information on a whole month, it means day>='2007-10-01' and day
> <'2007-11-01' but if there is no any quantity for some days a need to
> have 0 as a result. Assume that I have following records:

No problem, generate_series() can do the job. Example:

test=*# select * from t1;
d | val
------------+-----
2007-10-08 | 1
2007-10-13 | 5
(2 rows)

test=*# select current_date+s, coalesce(val,0) from
generate_series(0,5)s left outer join t1 on(current_date+s = d);
?column? | coalesce
------------+----------
2007-10-08 | 1
2007-10-09 | 0
2007-10-10 | 0
2007-10-11 | 0
2007-10-12 | 0
2007-10-13 | 5
(6 rows)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net