Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 1 to 4 of 4 total

Fri, Mar 6 2009 2:38 PM	Permanent Link
Michael Fullerton	Suppose I have a table with the fields Customer and VisitDate. Is it possible to build a query that shows all customers that have not visited for the lat 30 days? This query will not work: SELECT Customer FROM CustomerVisits WHERE VisitDate < CURRENT_DATE-30 Because if Ed visited 45 days ago and yesterday he will be listed. If it can't be done in DBISAM how about EDB?
Fri, Mar 6 2009 2:51 PM	Permanent Link
"Jeff Cook"	Michael Fullerton wrote: > Suppose I have a table with the fields Customer and VisitDate. Is it > possible to build a query that shows all customers that have not > visited for the lat 30 days? This query will not work: > > SELECT Customer FROM CustomerVisits WHERE VisitDate < CURRENT_DATE-30 > > Because if Ed visited 45 days ago and yesterday he will be listed. > > If it can't be done in DBISAM how about EDB? Something like this:- SELECT Customer, MAX(VisitDate) AS LastVisit FROM CustomerVisits GROUP BY Customer HAVING MAX(VisitDate) < CURRENT_DATE-30 .... don't use HAVING very much so I probably have the syntax all up the pooh! -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz + Joan and Jeff Cook The Cooks Oasis www.cookislandsoasis.com
Fri, Mar 6 2009 2:57 PM	Permanent Link
Joze	>SELECT Customer FROM CustomerVisits WHERE VisitDate < CURRENT_DATE-30 How about SELECT Customer FROM CustomerVisits WHERE VisitDate NOT BETWEEN :date1 AND :date2 and set paramater Date1 to CURRENT_DATE-30 and paramater Date2 to CURRENT_DATE Regards, Joze
Fri, Mar 6 2009 3:55 PM	Permanent Link
Michael Fullerton	On Fri, 6 Mar 2009 14:51:36 -0500, "Jeff Cook" <jeffc@aspect.co.nz> wrote: >Michael Fullerton wrote: > >> Suppose I have a table with the fields Customer and VisitDate. Is it >> possible to build a query that shows all customers that have not >> visited for the lat 30 days? This query will not work: >> >> SELECT Customer FROM CustomerVisits WHERE VisitDate < CURRENT_DATE-30 >> >> Because if Ed visited 45 days ago and yesterday he will be listed. >> >> If it can't be done in DBISAM how about EDB? > >Something like this:- > >SELECT Customer, MAX(VisitDate) AS LastVisit >FROM CustomerVisits >GROUP BY Customer >HAVING MAX(VisitDate) < CURRENT_DATE-30 > >... don't use HAVING very much so I probably have the syntax all up the >pooh! No that worked great Jeff. Thanks.