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