Hello Basoz,

Thank you for your assistance

I tested the three codes but it is gregory 's code which gives good results.

PS Izmir is a beautifull city.
i have a turkisch friend that invited me


merry christmas

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 = 0 TO 180

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

  ENDFOR


  *-- je veux touver toutes les dates comprises entre le {^2005/01/08}
  *-- et le {^2005/06/25} pour le resident 1753
  IF .T.
    CREATE CURSOR Etats (dDebut D, dFin D , iresidentid i , iid i )
    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 )
    INSERT INTO Etats VALUES ({^2006/01/10}, {^2007/05/25} , 1756 , 4 )
    INSERT INTO Etats VALUES ({^2006/05/30}, {^2007/06/25} , 1756 , 5 )
    INSERT INTO Etats VALUES ({^2001/01/08}, {^2003/03/30} , 2 	, 6 )
    INSERT INTO Etats VALUES ({^2003/04/01}, {^2004/03/30} , 2 	, 10 )

    *-- Here ddebut is NOT in ORDER but iid is just
    INSERT INTO Etats VALUES ({^2004/01/10}, {^2005/05/25} , 1776 , 7 )
    INSERT INTO Etats VALUES ({^2005/06/04}, {^2007/07/25} , 1776 , 8 )
    INSERT INTO Etats VALUES ({^2005/05/29}, {^2005/06/03} , 1776 , 9 )

  ELSE

    *-- For real test
    USE C:\cdbk90\amline\data1\Etats.DBF ORDER Resident IN 0


  ENDIF


  *------ Gregory
  && (1) Min and Max Dates/Resident
  && You may have this table
  && It gives beginning and ending date per Resident
  SELECT	iresidentid, ;
    MIN(dDebut)	AS	MinDate, ;
    MAX(dFin)	AS	MaxDate ;
    FROM Etats ;
    INTO CURSOR Accord ;
    GROUP BY 1

  && (2) create date cursor
  LOCAL i, DateFromTo[2]

  SELECT	MIN(MinDate), ;
    MAX(MaxDate) ;
    FROM Accord ;
    INTO ARRAY DateFromTo

  CREATE CURSOR Dates ( dDate D)

  FOR i = 0 TO DateFromTo[2] - DateFromTo[1]
    INSERT INTO Dates VALUES ( DateFromTo[1] + m.i )
  ENDFOR

  && (3)
  SELECT	Accord.iresidentid, ;
    dDate, ;
    iid ;
    FROM Accord ;
    JOIN Dates ON dDate BETWEEN MinDate AND MaxDate ;
    LEFT JOIN Etats ON ;
    ( Accord.iresidentid = Etats.iresidentid ) ;
    AND	( dDate BETWEEN dDebut AND dFin ) ;
    INTO CURSOR gregory ;
    ORDER BY 1, 2 ;
    HAVING ( iid IS NULL ) && if you leave this out, iId will be the corresponding entry in Etats

  BROWSE NOWAIT

  *-- Fabio
  SELECT DISTINCT dDate,iresidentid ;
    FROM dDate ;
    JOIN (SELECT A.iresidentid, A.dFin + 1 dHoleStart,;
    MIN(B.dDebut)-1 dholeEnd ;
    FROM Etats A  JOIN Etats B ON A.iresidentid = B.iresidentid AND  A.dFin + 1 < B.dDebut;
    GROUP BY 1,2) Holes ON dDate BETWEEN dHoleStart AND dholeEnd INTO CURSOR fabio



  BROWSE NOWAIT


  *-- Cetin
  SELECT DISTINCT c1.dDate,c1.iresidentid ;
    FROM (SELECT dDate, iresidentid FROM dDate,Etats) c1 ;
    LEFT JOIN ;
    (SELECT iresidentid, MIN(dDebut) AS dDebut,MAX(dFin) AS dFin ;
    FROM Etats GROUP BY 1) e1 ;
    ON e1.iresidentid = c1.iresidentid AND ;
    c1.dDate BETWEEN e1.dDebut AND e1.dFin ;
    WHERE ISNULL(e1.iresidentid) ;
    ORDER BY 2,1   INTO CURSOR Cetin


  BROWSE NOWAI


  SET

>PMFJI do you mean one of these? (I checked the jpg but couldn't understand)
>

>Select Distinct c1.dDate,c1.iresidentid ;
>  FROM (Select dDate, iresidentid From dDate,Etats) c1 ;
>  left Join ;
>  (Select iresidentid, Min(dDebut) As dDebut,Max(dFin) As dFin ;
>  FROM Etats Group By 1) e1 ;
>  On e1.iresidentid = c1.iresidentid And ;
>  c1.dDate Between e1.dDebut And e1.dFin ;
>  WHERE Isnull(e1.iresidentid) ;
>  ORDER By 2,1
>
>Select Distinct c1.dDate,c1.iresidentid ;
>  FROM (Select dDate, iresidentid From dDate,Etats) c1 ;
>  left Join Etats e1 On ;
>  e1.iresidentid = c1.iresidentid And ;
>  c1.dDate Between e1.dDebut And e1.dFin ;
>  WHERE Isnull(e1.iresidentid) ;
>  ORDER By 2,1
>

Cetin
>
>>Sorry for the disturbance...
>>
>>If the ddebut aren't in order we have a problem in results...
>>
>>Try it fabio
>>
>>Merry chistmas....
>>bernhart
>>
>>

>>
>>CREATE CURSOR Etats (dDebut D, dFin D , iresidentid i , iid i )
>>    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 )
>>    INSERT INTO Etats VALUES ({^2006/01/10}, {^2007/05/25} , 1756 , 4 )
>>    INSERT INTO Etats VALUES ({^2006/05/30}, {^2007/06/25} , 1756 , 5 )
>>    INSERT INTO Etats VALUES ({^2001/01/08}, {^2003/03/30} , 2 	, 6 )
>>
>>    *-- Here ddebut is NOT in ORDER but iid is just
>>    INSERT INTO Etats VALUES ({^2004/01/10}, {^2005/05/25} , 1776 , 7 )
>>    INSERT INTO Etats VALUES ({^2005/06/04}, {^2007/07/25} , 1776 , 8 )
>>    INSERT INTO Etats VALUES ({^2005/05/26}, {^2005/06/03} , 1776 , 9 )
>>
>>  SELECT dDate,iresidentid ;
>>    FROM dDate ;
>>    JOIN (SELECT A.iresidentid, IIF( A.dFin = {} ,  d_fin ,  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,2) Holes ON dDate BETWEEN dHoleStart AND dholeEnd INTO CURSOR fabio
>>
>>
>>

>>
>>
>>
>>>>Hello Fabio,
>>>>
>>>>I have a "Cannot GROUP by aggregate field (Error 1846)" (VFP 9 PACK 1 and engenbehavior TO 90 ) ?
>>>>
>>>>

>>>>>SELECT dDate,iresidentid ;
>>>>>  FROM dDate ;
>>>>>  JOIN (SELECT A.iresidentid,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,2) Holes ON dDate BETWEEN dHoleStart AND dholeEnd
>>>>

>>>>
>>>>Bonne journée
>>>>
>>>>bernhart
>>>
>>>It work on my VFP9 sp1! Why ?