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



>>Hi all,
>>
>>
>>i have Ddate.dbf ( ddate D )
>>European Date
>>
>>01/01/2005
>>02/01/2005
>>03/01/2005
>>04/01/2005
>>05/01/2005
>>06/01/2005
>>07/01/2005
>>08/01/2005
>>09/01/2005
>>
>>i have etats.dbf ( ddebut D , Dfin D )
>>
>>dDebut dfin
>>02/01/2005 04/01/2005
>>07/01/2005 08/01/2005
>>
>>I want all dates that are NOT include in etats.dbf betw MIN(ddebut ) 02/01/2005 and Max( dfin ) 08/01/2005 =>
>>EX 05/01/2005 06/01/2005
>>
>>
>>How can i do it with a sql syntaxe ?
>>
>>Thank in advance
>>
>>
>>bernhart
>
>

>        local i
>	create cursor dDate ( ddate D)
>	for i = 1 to 9
>		insert into dDate values( {^2005/01/01} - 1 + m.i )
>	endfor
>	
>	Create cursor Etats (dDebut D, dFin D)
>	insert into Etats values ({^2005/01/02}, {^2005/01/04})
>	insert into Etats values ({^2005/01/07}, {^2005/01/08})
>	
>	select	min(dDebut)	as	MinDate, ;
>		max(dFin)	as	Maxdate ;
>	    from Etats ;
>	    into cursor MinMax
>		
>	&& (1)
>	select	dDate, ;
>		dDebut ;
>	    from  dDate ;
>	  	left join Etats on ( ddate between dDebut and dFin ) ;
>    	    into cursor tmp1 ;
>	    where	( ddate between MinMax.MinDate and MinMax.Maxdate ) ;
>	    order by 1 ;
>	    having ( dDebut is null )
>	
>	&& (2)
>	select	dDate ;
>	    from  dDate DD1;
>	    into cursor tmp2 ;
>	    where	( DD1.ddate between MinMax.MinDate and MinMax.Maxdate ) ;
>	    	and	( DD1.ddate not in ;
>				( select ddate ;
>					from dDate DD2 ;
>						join Etats on ( DD2.ddate between dDebut and dFin ) ;
>				) ;
>			) ;
>	    order by 1
>