troubleshooting Question

Subquery doesn't work right - does weird things! Help!

Avatar of stankstank
stankstank asked on
Microsoft SQL ServerMicrosoft SQL Server 2005
4 Comments1 Solution228 ViewsLast Modified:
Dear Experts,

I am trying to make a query which returns all contacts who have referred at least 3 contacts within the last XXX days.  In this example, I have entered the value of 360 (1 year).  I have not even tried to figure out how to calculate the total number of referrals given becuase it gets really weird then.

It does not pull the values correctly...The query I am posting LOOKS like it works...but it doesn't.  It's just not accurate and I don't know what I have done wrong.  Please view line 17 where the code is 'WHERE Referred_By = Contact_Auto_ID)' - notice that the ')' character closes the select statement.  If I put this anywhere else, I get a really jacked up set of data...  Just an example of one problem I am having.

Here is how my table looks (I have taken out some of the columns which don't matter like names & phone number columns... you can see those in the select statement on the link)

[Contacts].Contacts
--------------------
Contact_Auto_ID (pk, int)
Date_Added (smalldatetime)
Referred_By (int - fk to Contact_Auto_ID in this table)

The column 'Referred_By' must be related to Contact_Auto_ID so that's why it's a FK to a column in the same table...  There is a default value in Contact_Auto_ID of 0.  That's how we have Referred_By set to 0 in the columns which weren't referred by another contact.

Another weird thing..in the current query, the column 'PhoneHome' has a value of null and it shows the record.  If I enter a phone number, that record goes away.  I have NO IDEA WHY!!!

I have been working with this for several hours now and still no luck...  Please help!

Here is the link to my query:
http://75.33.104.230/code/query.aspx
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros