Bingo

Thank fabio

If i want the field iresidentid in the query , how can i do ?

SELECT dDate ,;
MAX( B.iresidentid);
  FROM dDate ;
  JOIN ( SELECT A.dFin+1 dHoleStart,MIN( B.dDebut )-1 dholeEnd  ;
	FROM Etats A JOIN Etats B ON A.iresidentid = B.iresidentid AND A.dFin < B.dDebut;
	GROUP BY 1  ) Holes ON dDate BETWEEN dHoleStart AND dholeEnd

bernhart

>>Fabio,
>>
>>
>>For iresidentid = 1753 i want 01/04/2005 to 09/04/2005...
>>FOR iresidentid = 1754 i want nothing ...
>>
>>bernhart
>>
>>
>
>The example's data are too much little,
>however:
>

>  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 )
>
>* not a very efficient solution
>
>SELECT dDate ;
>  FROM dDate ;
>  JOIN (SELECT A.dFin+1 dHoleStart,MIN(B.dDebut)-1 dholeEnd ;
>	FROM Etats A JOIN Etats B ON A.iresidentid = B.iresidentid AND A.dFin < B.dDebut;
>	GROUP BY 1) Holes ON dDate BETWEEN dHoleStart AND dholeEnd
>