Hi 
i have a table of dates let's say:

1/1/2004
8/1/2004
15/1/2004
29/1/2004
5/2/2004
12/2/2004

I am searching for a way to have the minimum date and maximum date for dates seperated by one week whitout gaps between them.
which will give the following output:
1/1/2004 , 15/1/2004
29/1/2004 , 12/2/2004 

Any ideas??
Could this be done with aggregates ??







 
Hi 
i have a table of dates let's 
say:
 
1/1/2004
8/1/2004
15/1/2004
29/1/2004
5/2/2004
12/2/2004
 
I am searching for a way to have the minimum date 
and maximum date for dates seperated by one week whitout gaps between 
them.
which will give the 
following output:
1/1/2004 , 15/1/2004

29/1/2004 , 12/2/2004 
 
Any ideas??
Could this be done with aggregates ??
Wed, 30 Jun 2004 08:39:29 +0200
"Najib Abi Fadel" <nabifadel@usj.edu.lb>
Creating a selective aggregate ??

"Najib Abi Fadel" <nabifadel@usj.edu.lb> writes:

> Any ideas??
> Could this be done with aggregates ??

I think you're going to have to write code that reads all the records, keeps
state representing the current period start and the last seen end date and
outputs data every time it sees a gap like you want.

This could be a set returning plpgsql or plperl or plwhatever function, but it
may be easier to write it in your application level code.

-- 
greg


03 Jul 2004 10:31:32 -0400
Greg Stark <gsstark@mit.edu>
Re: Creating a selective aggregate ??

-----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@turnstep.com
PGP Key: 0x14964AC8 200407031237
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFA5uD9vJuQZxSWSsgRAqpLAKDBm0dxgiigfSL6cnhz83pmKV1KLgCgogD2
etbk3BJiWm5bplCEIEFXbbE=
=HT+N
-----END PGP SIGNATURE-----



Sat,  3 Jul 2004 16:36:36 -0000
"Greg Sabino Mullane" <greg@turnstep.com>
Re: Creating a selective aggregate ??