select Accord.iresidentid, ; T.cnomteleph, ; COUNT( dDate ) AS NombreParJour ; from Accord ; join Dates on dDate between MinDate and MaxDate ; join Etats on ; ( Accord.iresidentid = Etats.iresidentid ) ; and ( dDate between dDebut and dFin ) ; JOIN Resident R ON R.iid = Accord.iresidentid ; JOIN Teleph T ON R.itelephoneid = T.iid ; into cursor xxx ; group by 1, 2 ; having ( NombreParJour > 1 )>Gregory,
> && (3) > SELECT dDate,; > T.cnomteleph ,; > Accord.iresidentid, ; > COUNT( dDate ) AS NombreParJour ; > FROM Accord ; > JOIN Dates ON dDate BETWEEN MinDate AND MaxDate ; > LEFT JOIN Etats ON ; > ( Accord.iresidentid = Etats.iresidentid ) ; > AND ( dDate BETWEEN Etats.dDebut AND Etats.dFin ) ; > LEFT JOIN Resident R ON R.iid = Accord.iresidentid ; > LEFT JOIN Teleph T ON R.itelephoneid = T.iid; > INTO CURSOR gregory ; > GROUP BY 1 , 2, 3 ; > HAVING NombreParJour > 1 >>
>> >> 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/31}, {^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) >> 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 ) >> >> >> *=brow() >> >> >> && (4) >> select tmp.iresidentid, ; >> dDate, ; >> max(E1.iid) as Etat_apres ; >> from tmp ; >> left join Etats E1 ; >> on (tmp.iresidentid = E1.iresidentid ) ; >> and (tmp.dDate > E1.dFin) ; >> into cursor trou_tmp ; >> group by 1, 2 >> >> select iresidentid, ; >> Etat_apres, ; >> min(dDate) as DateFrom, ; >> max(dDate) as DateTo ; >> from trou_tmp ; >> into cursor trou ; >> group by 1, 2 >> >> *=brow() >>