Bernhart,

You are not using the code I wrote

Why do you use an INNER JOIN on Etats together with the IS NULL in the having clause ? That will never work . It should be a LEFT JOIN
_________
>Thank gregory for your reply but i have stillproblems....
>
>look at :
>http://www.amline.be/UT_DOC/utsql2.jpg
>
>
>

>
>
>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 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 )
>
>
>  SELECT	MIN(dDebut)	AS	MinDate, ;
>    MAX(dFin)	AS	Maxdate ,;
>    iresidentid;
>    FROM Etats ;
>    GROUP BY 3;
>    INTO CURSOR MinMax
>
>  *!*	  && (1)
>  *!*	  SELECT  dDate ;
>  *!*	    FROM  dDate JOIN ( SELECT MIN( dDebut ) MinDate , MAX( dFin ) Maxdate FROM Etats ) MinMax;
>  *!*	    ON NOT dDate BETWEEN MinDate AND Maxdate INTO CURSOR temp
>
>
>  SELECT Mm.iresidentid, ;
>    D.dDate ;
>    FROM MinMax Mm ;
>    JOIN dDate D;
>    ON ( D.dDate BETWEEN MinMax.MinDate AND MinMax.Maxdate ) ;
>    INNER JOIN Etats E ;
>    ON ;
>    ( ( Mm.iresidentid = Etats.iresidentid ) ;
>    AND	;
>    ( D.dDate NOT BETWEEN E.dDebut  AND E.dFin ) );
>    INTO CURSOR tmp ;
>  HAVING ( d.dDebut IS NULL )
>
>

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