SELECT NoMember, MAX(Creation) MaxCreation; FROM Login ; WHERE NoMember NOT IN ( SELECT DISTINCT noMember ; FROM Login ; WHERE Creation >= DATE() - 120 AND ; BETWEEN(NoMember, lnNumerstart, lnNumberEnd)); WHERE BETWEEN(NoMember, lnNumerstart, lnNumberEnd) ; GROUP BY NoMember ; INTO CURSOR NonActiveMembers SELECT M.*, MaxCreation ; FROM Member M INNER JOIN NonActiveMembers N ON N.NoMember=M.Numero ; WHERE Member.mailing = 1 INTO CURSOR TempTough this strategy actually executes three queries, it might be faster as the Active Members are filtered out before the group by. Whether it improves performance might depend on the balance of Active and nonActive members.
SELECT * ; FROM Member WHERE BETWEEN(Numero, lnNumerstart, lnNumberEnd) AND mailing = 1 ; INTO CURSOR MemberSelection SELECT M.*, MAX(Creation) as LoginCreation ; FROM MemberSelection M INNER JOIN Login L ; ON Numero = NoMember ; GROUP BY Numero ; HAVING Creation < DATE() - 120 ; INTO CURSOR TempIn either case you'll need the following indices:
>SELECT MEMBER.*,MAX(LOGIN.CREATION) AS LOGINCREATION FROM LOGIN; > INNER JOIN MEMBER ON LOGIN.NOMEMBER=MEMBER.NUMERO; > WHERE MEMBER.NUMERO>=lnNumberStart AND MEMBER.NUMERO<=lnNumberEnd; > AND MEMBER.MAILING=1; > HAVING LOGINCREATION< DATE()-120; > GROUP BY 1 INTO CURSOR TEMP >