I received this question from a long time blog reader, “Do you have a SQL Query that can list the Companies where there has been NO CHANGE, no pending record entered or History Record in the last year?”
Well, no I don’t, but let’s see what we can do!
So here’s the query:
SELECT c1.company, c1.contact, c1.phone1, c1.address1, c1.city, c1.state, c1.zip, c1.key1, c1.accountno FROM contact1 c1 WHERE lastdate < getdate() - 365 AND c1.accountno NOT IN (SELECT accountno FROM cal WHERE createon >= getdate() - 365) AND c1.accountno NOT IN (SELECT accountno FROM conthist WHERE createon >= getdate() - 365) AND c1.key1 <> 'Unqualified' ORDER BY c1.company, c1.contact
So a couple of things to note. You’ll notice I’m using getdate() – 365 to automatically go back one year, you could replace this with a date, e.g. < 1/1/2015, if you want to hard code the dates. Also, I am excluding any contact marked Unqualified in the key1 field for those contacts that you do not want to contact often.