homepage Welcome to WebmasterWorld Guest from 54.197.171.109
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
SQL Statement Help
To Exist or not to Exist, that is the question
Anolonda




msg:943863
 8:20 pm on May 16, 2005 (gmt 0)

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




msg:943864
 2:34 pm on May 17, 2005 (gmt 0)

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




msg:943865
 6:21 pm on May 17, 2005 (gmt 0)

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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved