asked on SQL - query to remove ID that has been contacted
I have a table in a SQL database that stores the number of times a client is contacted.
It looks similar to this:
ID Date Status
1001 2014-05-01 Not Contacted
1001 2014-05-02 Not Contacted
1001 2014-05-04 Contacted
1005 2014-05-04 Not Contacted
1005 2014-05-05 Contacted
1007 2014-05-07 Not Contacted
1007 2014-05-08 Not Contacted
1009 2014-05-08 Not contacted
I want to select only those ID's that have not been contacted, so if you look at my example. ID 1001 has been attempted 3 times. but since t has been contacted, I want to exclude it. ID 1007 and 1009 have not been contacted so those are the only 2 I want to return. Any ideas on how to do this?
It looks similar to this:
ID Date Status
1001 2014-05-01 Not Contacted
1001 2014-05-02 Not Contacted
1001 2014-05-04 Contacted
1005 2014-05-04 Not Contacted
1005 2014-05-05 Contacted
1007 2014-05-07 Not Contacted
1007 2014-05-08 Not Contacted
1009 2014-05-08 Not contacted
I want to select only those ID's that have not been contacted, so if you look at my example. ID 1001 has been attempted 3 times. but since t has been contacted, I want to exclude it. ID 1007 and 1009 have not been contacted so those are the only 2 I want to return. Any ideas on how to do this?
Microsoft SQL ServerSQL
Last Comment
PortletPaul
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
@PortletPaul: what if they need to retrieve other columns alongside with id
>>from the question: "I want to select only those ID's that have not been contacted"
extra columns aren't requested, and probably not needed
Let's assume I haven't contacted you for the last month; do I really need 28/29/30 or 31 records to tell me I haven't contacted you? :-)
The group by approach could return min(date)/max(date) which might be useful (perhaps)
extra columns aren't requested, and probably not needed
Let's assume I haven't contacted you for the last month; do I really need 28/29/30 or 31 records to tell me I haven't contacted you? :-)
The group by approach could return min(date)/max(date) which might be useful (perhaps)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Oh well it appears the debate is academic.
@Jasmin01 if you do only want the ID, then my suggestion is more efficient (faster)
@Jasmin01 if you do only want the ID, then my suggestion is more efficient (faster)
Open in new window