Thank Fabio,

look at :
http://www.amline.be/UT_DOC/utsql1.jpg

I want for iresidentid = 1753 =>
01/04/2005 TO 09/04/2005 ( But NOT 01/01/2005 TO 08/01/2005 )
IN temp , it give me bads results....

I want result also for each iresidentid ( 1753 , 1754 etc....

A idea ?

Good morning

bernhart


>>thank gregory ,
>>
>>i have still problems.
>>
>>

>>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 )
>>
>>
>>  SELECT MIN(dDebut)	AS	MinDate, ;
>>    MAX(dFin)	AS	Maxdate ,;
>>    iresidentid;
>>    FROM Etats ;
>>    GROUP BY 3;
>>    INTO CURSOR MinMax
>>
>>  && (1)
>>  SELECT	dDate, ;
>>    dDebut ;
>>    FROM  dDate ;
>>    LEFT JOIN Etats ON ( dDate BETWEEN Etats.dDebut AND Etats.dFin ) ;
>>    INTO CURSOR tmp1 ;
>>    WHERE	( dDate BETWEEN MinMax.MinDate AND MinMax.Maxdate ) AND ;
>>    MinMax.iresidentid = Etats.iresidentid;
>>    ORDER BY 1 ;
>>    HAVING ( dDebut IS NULL )
>>
>>

>>
>>The rsult is not good ....
>>
>>A solution ?
>>
>>bernhart
>>
>>
>

>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 )
>
>  SELECT  dDate ;
>    FROM  dDate JOIN (SELECT MIN(dDebut) MinDate , MAX(dFin)  Maxdate FROM Etats ) MinMax;
>    ON NOT dDate BETWEEN MinDate AND Maxdate
>