SQL Statement Help

To Exist or not to Exist, that is the question

[Anolonda]

Hello All,

I am working on a SQL statement that has me pulling out what is left of my hair. A client wants a callsheet where he can enter calls (with the date the call was made) to his contacts. The idea being, he has a page which displays any contact that has not been called in the last 90 days.

I have a Access table (CallNotesTable) with four columns: CallID, ContactID, DateEntered, CallNotes

The SQL statement seemed easy at first:

SELECT ContactID FROM CallNotesTable WHERE EnteredDate NOT BETWEEN Date() AND DateAdd('m',-3,date())

However, since all calls are dated when entered, this SQL statement pulls all DateEntered made prior to three months ago, even if there has been a subsequent call made within the last three months.

So what I need is for the SQL to differentiate between a ContactID that HAS a DateEntered prior to three month with no DateEntered AFTER three months.

I have looked at the WHERE EXISTS statements but to no avail; my mind starts to turn to goo. I think the WHERE EXISTS statement will run a sub query that will allow me to differentiate, but I can't seem to determine the correct syntax:

SELECT ContactID FROM CallNotesTable WHERE EXISTS (SELECT CallId FROM CallNotesTable WHERE EnteredDate NOT BETWEEN Date() AND DateAdd('m',-3,date())) AND NOT EXISTS (SELECT CallId FROM CallNotesTable WHERE EnteredDate BETWEEN Date() AND DateAdd('m',-3,date()))
ORDER BY ContactID

Can anyone out there give a SQL rookie a hint?

[aspdaddy]

Think of it as sets, as sql is based on sets.

You need a set of all the the contact id's for calls made in the last 90 days. You then need the set of 'all' the contact id's. You then need to take one from the other - set difference :) Using SQL views -

Slect contact_id FROM vw_allcontacts
WHERE contact_id NOT IN
(SELECT contact_id FROM vw_callsinlast90days)

[Anolonda]

Thanks AspDaddy! You are a life saver. I knew nothing about Views but they are a wonderful time saver.

I really appreciate you taking the time to point me in the right direction. It now works like a charm!