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@krap.dk - svenne@krap.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