Filter/SQL query using conthist data

[tantaros]

I am trying to create a filter using the most recent historical activity date to determine status of an account. Then I use that filtered list to do a global replace. As far as I can tell from my documentation filtering on this table can only be done through groups or sql. Since the dates are dynamic I don't believe I can use groups, ?. As a result I have been working with the following:

The following query yields the info I need for the ondate:

SELECT conthist.accountno, max(conthist.ondate) FROM conthist
GROUP BY conthist.accountno HAVING max(conthist.ondate)<"03/15/2005"

But when I try to put it together with data from contact1 I can't seem to make it functional (see attempt below):

SELECT contact1.accountno, contact1.contact, contact1.company, contact1.key4, conthist.accountno
FROM contact1, conthist
WHERE contact1.accountno=conthist.accountno
AND conthist.accountno
IN(SELECT conthist.accountno, max(conthist.ondate)
FROM conthist
GROUP BY conthist.accountno HAVING max(conthist.ondate)>"03/15/2005");

Additionally, I would like to make the limiting date a function of today's date minus 6 months... but DATE, TODAY, NOW and SYSDATE are all giving me errors that they are not functions.

This is probably an easy fix, but I am not a programmer so please be gentle!

 

[richardy]

Hi,

Are you using the SQL version of GoldMine? If your using dBase then you can only use simple queries ie IN statements wont work properly.

I'd also try:

SELECT contact1.accountno, contact1.contact, contact1.company, contact1.key4, contact1.accountno
FROM contact1
WHERE contact1.accountno
IN (SELECT conthist.accountno
FROM conthist WHERE conthist.ondate>"03/15/2005")



Regards,

Richard.
PRIOR Analytics UK

http://www.prior-analytics.com

Winners of the LBA Customer Service Award.

 

[tantaros]

That worked like a charm! Thanks.

Now all I am missing is the date command to give me TODAY-180 (6 months from the date)

Any idea why its telling me DATE() is not a valid function?

Laura

 

[richardy]

Use Getdate()

Regards,

Richard.
PRIOR Analytics UK

http://www.prior-analytics.com

Winners of the LBA Customer Service Award.