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 ) && (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 tmp ; order by 1, 2 ; having ( iId is null ) && if you leave this out, iId will be the corresponding entry in Etats>>Ok, donc si j'ai bien compris, tu as quelque part un table Accord par resident (Resident I, ddebut D, dFin D)
>>>>> >>>>>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 ) >>>>> 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 ) >>>>> >>>>> && (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 ) >>>>> >>>>>>>>>> SET