-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> I am searching for a way to have the minimum date and maximum
> date for dates seperated by one week whitout gaps between them.
This is not something you can accomplish (easily) with just SQL,
so you will need some sort of higher language. Here's a little
function I came up with, which might do what you want:
CREATE OR REPLACE FUNCTION noweekgaps() RETURNS SETOF TEXT AS '
DECLARE
startday TEXT;
endday TEXT;
lastone INT;
mydiff INT;
myrec RECORD;
BEGIN
FOR myrec IN SELECT TO_CHAR(d, \'Mon DD, YYYY\') AS z,
TO_CHAR(d,\'J\')::integer AS j FROM t ORDER BY d ASC LOOP
IF startday IS NULL THEN
startday := myrec.z;
ELSE
mydiff := myrec.j - lastone;
IF mydiff > 7 THEN
RETURN NEXT startday || \' - \' || endday;
startday := myrec.z;
END IF;
END IF;
lastone := myrec.j;
endday := myrec.z;
END LOOP;
IF endday IS NOT NULL THEN
RETURN NEXT startday || \' - \' || endday;
END IF;
RETURN;
END;
' LANGUAGE plpgsql;
- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200407031237
-----BEGIN PGP SIGNATURE-----
iD8DBQFA5uD9vJuQZxSWSsgRAqpLAKDBm0dxgiigfSL6cnhz83pmKV1KLgCgogD2
etbk3BJiWm5bplCEIEFXbbE=
=HT+N
-----END PGP SIGNATURE-----