&& (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 ) ENDFORC'est joli et bien écrit !
>>>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 >>>>>>
>>>>>>>> >>>>>>>>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