>Bonsoir gregory,
>
>J'ai besoin de connaître la valeur de etats.iid pour aller rechercher l'accord ( etats.dbf ) dans lequel il y a un "trou".
>cependant dans ta requête la clause "having ( iId is null )" donne ( à juste titre ) des etats.iid = .Null.
>
>Une idée ?
>
>bernhart

Ceci ?

	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/03/31}, {^2005/04/02} , 1753 , 2 )
	INSERT INTO Etats VALUES ({^2005/04/10}, {^2005/06/25} , 1753 , 3 )
	
	INSERT INTO Etats VALUES ({^2005/01/10}, {^2005/06/24} , 1754 , 4 )
	INSERT INTO Etats VALUES ({^2005/06/25}, {^2005/06/25} , 1754 , 5 )
	
	

	
	&& (1) Min and Max Dates/Resident
	Create Cursor Accord ;
		( iresidentid	I, MinDate D, MaxDate D)
		
	insert into Accord Values( 1753, {^2005/01/01}, {^2005/06/30})
	insert into Accord Values( 1754, {^2005/01/10}, {^2005/06/25})
	
	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 tmp ;
  		order by 1, 2 ;
  		having ( iId is null ) 
  	
  	
  	*=brow()


	&& (4)
	select	tmp.iresidentid, ;
			dDate, ;
			max(E1.iid)	as Etat_apres ;
		from tmp ;
			left join Etats E1 ;
				on 		(tmp.iresidentid = E1.iresidentid ) ;
					and (tmp.dDate > E1.dFin) ;
		into cursor trou_tmp ;
		group by 1, 2
	
	select	iresidentid, ;
			Etat_apres, ;
			min(dDate)	as	DateFrom, ;
			max(dDate)	as	DateTo ;
		from  trou_tmp ;
		into cursor trou ;
		group by 1, 2
	
	*=brow()