SQL query for older contacts.

[XopherS]

This is... convoluted.

I have a customer database in GoldMine.  I want to get all old customers so that I may deactivate them.  Criteria for "old":

- If they do not have any *call* activities in the History tab, then the created date should be 12/31/2002 or earlier.  (We've arbitrarily decided on a four-year oldness date.)

- If they do have *call* activities, then the most recent activity *for our supported products* should be no later than 12/31/2002.

I'm working this in SQL, so here's the tables/fields information:

- contact1:
-- accountno
-- contact
-- createon

- conthist:
-- srectype
-- createon
-- actvcode

Restrictions:
- where srectype = 'C' [for Call]
- where createon < '1/1/2003' [in either table]
- where actvcode <> 'RYL' [the code for the nonsupported product]

Where do I even begin?

 

[DataDivaJill]

Try this, not sure about the actvcode part.
The inside select is all the stuff that is and then you look for what isn't for contact1.

Make sure your createon for contact1 is correct.
I found mine to be bad.
The GM accountno is the create date.
If you do web imports createon doesn't get populated at all. I would export accountno and createon for all your records and proof.
If need to be corrected you could modify the accountno via the accountno and reimport.
Just incase you dont know, the first 1 char of account is the centry portion of the year (9=1900,A=2000)
next is the last portion of the year (99=1999, A6=2006)
next 2 represent the month and then the next 2 represent the day.


select createon,company,contact,accountno
from contact1
where accountno not in
(select accountno
from conthist
where ondate > '1/1/2003'
and rectype='C' and actvcode='RYL')
and createon < '1/1/2003'
order by createon

Good Luck
Jill