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