Gregory,

sorry for my poor english but my parents wanted that I study Hollands in place of English ...:)

Please try this code , and see the ( bad ) result For iresidentid = 2

Please help me , c'est noel et je dois m'occupper des enfants ....:)

bernhart

CREATE CURSOR Etats (dDebut D, dFin D , iresidentid i , iid i )


  *-- je veux touver toutes les dates comprises entre le {^2005/01/08}
  *-- et le {^2005/06/25} pour le resident 1753
  
  INSERT INTO Etats VALUES ({^2005/01/08}, {^2005/03/30} , 1753 , 1 )
  INSERT INTO Etats VALUES ({^2005/04/10}, {^2005/06/25} , 1753 , 2 )
  INSERT INTO Etats VALUES ({^2005/01/10}, {^2005/06/25} , 1754 , 3 )
  INSERT INTO Etats VALUES ({^2006/01/10}, {^2007/05/25} , 1756 , 4 )
  INSERT INTO Etats VALUES ({^2006/05/30}, {^2007/06/25} , 1756 , 5 )
 INSERT INTO Etats VALUES ({^2001/01/08}, {^2003/03/30} , 2 , 6 )

  && (1)
  && min and max dates:  entre le {^2005/01/08} et le {^2005/06/25} pour chaque resident
  SELECT MIN(dDebut)	AS	MinDate, ;
    MAX(dFin)	AS	Maxdate ;
    FROM Etats ;
    INTO CURSOR MinMax

  && (2)
  SELECT DISTINCT iresidentid ;
    FROM Etats ;
    INTO CURSOR Resident ;

  && (3)
  SELECT Resident.iresidentid, ;
    dDate, ;
    dDebut ;
    FROM Resident ;
    JOIN dDate ;
    ON ( dDate BETWEEN MinMax.MinDate AND MinMax.Maxdate ) ;
    LEFT JOIN Etats ON ;
    (	(Resident.iresidentid = Etats.iresidentid) ;
    AND	;
    ( dDate BETWEEN dDebut  AND dFin ) ;
    ) ;
    INTO CURSOR tmp ;
    HAVING ( dDebut IS NULL )

SET