>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/30}, {^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) b > >select Accord.iresidentid, ; > dDate, ; > !isnull(iid) as Trouve, ; > count(*) as NombreParJour ; > 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 xxx ; > group by 1,2, 3 ; > having ( !Trouve or ( NombreParJour > 1) ) > >&& !Trouve = trou >&& NombreParJour > 1 && doubles > >>