Re: Birthsday list

I kind of found the answer myself ... here are some snipplets ...

the table and the data :

CREATE SEQUENCE "friends_friendid_seq" start 1 increment 1 maxvalue
2147483647 minvalue 1 cache 1 ;

CREATE TABLE "friends" (
"friendid" integer DEFAULT
nextval('"friends_friendid_seq"'::text) NOT NULL,
"friendname" character varying,
"dateofbirth" timestamp with time zone,
Constraint "friends_pkey" Primary Key ("friendid")
);

COPY "friends" FROM stdin;
1 Tony 1978-01-28 00:00:00+01
2 Gary 1966-06-04 00:00:00+01
3 Jodie 1979-01-11 00:00:00+01
\.

My query, works but looks clumbersome.. can it be made smarter ?

select * from (
(select *,date_part('year',now()) - date_part('year', dateofbirth) as
age, date_part('doy',dateofbirth)-date_part('doy',now()) as daystogo
from friends where date_part('doy',dateofbirth) >=
date_part('doy',now()))
union
(select *,date_part('year',now()) - date_part('year', dateofbirth ) +1
as age, date_part('doy',dateofbirth)-date_part('doy',now()) +
date_part('day', (now() + '1 year'::interval)::timestamp - now()) as
daystogo from friends where date_part('doy',dateofbirth) <
date_part('doy',now())))
r order by r.daystogo

Tia

Svenne
--
Mail usenet(at)krap(dot)dk - svenne(at)krap(dot)dk - PGP key id : 0xDF484022
ICQ: 5434480 - http://www.krap.dk - http://www.krap.net
PGP Key http://keys.pgp.dk:11371/pks/lookup?op=get&search=0xDF484022