Bernhart,

(1) Determine min and max dates to be used in the query
(2) Residents that participate
(3) Expand all the dates (between min and max from (1)) for each resident
Try to find an Etat for that date, null = no Etat

    LOCAL i,;
    D_debut,;
    d_fin

  D_debut = {^2005/01/01}
  d_fin = {^2005/07/01} - 1


  CREATE CURSOR dDate ( dDate D )

  FOR i = 1 TO 180

    INSERT INTO dDate VALUES( {^2005/01/01} - 1 + m.i )

  ENDFOR



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


  *-- je veux touver toutes les dates NON comprises entre le {^2005/01/08}
  *-- et le {^2005/06/25} pour chaque resident.


  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 , 2 )


   && (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 )