1

Edited

I am running into an error and I know what is happening but I can't see what is causing it. Below is the sql code I am using. Basically I am getting the general results I want, however I am not accurately giving the query the correct 'where' clause.

If this is of any assistance. The count is coming out as this:

Total       Tier
1           High
2           Low

There are 4 records in the Enrollment table. 3 are active, and 1 is not. Only 2 of the records should be displayed. 1 for High, and 1 for low. The second Low record that is in the total was flagged as 'inactive' on 12/30/2010 and reflagged again on 1/12/2011 so it should not be in the results. I changed the initial '<=' to '=' and the results stayed the same.

I need to exclude any record from Enrollments_Status_Change that where the "active_status" was changed to 0 before the date.

SELECT COUNT(dbo.Enrollments.Customer_ID) AS Total,
       dbo.Phone_Tier.Tier 
  FROM dbo.Phone_Tier as p
  JOIN dbo.Enrollments as eON p.Phone_Model = e.Phone_Model
       WHERE (e.Customer_ID NOT IN
               (Select Customer_ID
                From dbo.Enrollment_Status_Change as Status
                Where (Change_Date >'12/31/2010')))
 GROUP BY dbo.Phone_Tier.Tier

Thanks for any assistance and I apologize for any confusion. This is my first time here and i'm trying to correct my etiquette on the fly.

Improve this question
13
  • In future, please format your code to make it easy for others to read. I've done this for you in this instance. Commented Feb 18, 2011 at 21:50
  • 2
    can't you just remove the Enrollment_Status_Change table from your JOIN and get the results you want? I don't see anywhere in your query that you are using information in the main query from this table....just in the EXISTS clause Commented Feb 18, 2011 at 21:52
  • 1
    There is no point in selecting specific columns in the EXISTS sub-query. Also, that subquery isn't connected to the outer query in any way, so the EXISTS is either always true or always false, regardless of the contents of the outer query's rows. I suspect this is not what you intended. Commented Feb 18, 2011 at 21:54
  • 1
    I concur with the other commenters who are saying leave out the Enrollment_Status_Change Left Join. You don't use it in your query. Saying you need it because it is "time-based" does not make sense. Either you need it in your particular query or you don't. You may need it in other queries, but not in this one. Commented Feb 18, 2011 at 21:56
  • 1
    @Randal: If you accidentally hit Enter while writing a comment, you can edit the comment for up to five minutes. Commented Feb 18, 2011 at 21:56

2 Answers 2

Reset to default
2

If you don't want any of the fields from that table dbo.Enrollment_Status_Change, and you don't seem to use it in any way — why even include it in the JOINs? Just leave it out.

Plus: start using table aliases. This is very hard to read if you use the full table name in each JOIN condition and WHERE clause.

Your code should be:

SELECT 
    COUNT(e.Customer_ID) AS Total, p.Tier 
FROM 
    dbo.Phone_Tier p
INNER JOIN 
    dbo.Enrollments e ON p.Phone_Model = e.Phone_Model 
WHERE 
    e.Active_Status = 1 
    AND EXISTS (SELECT DISTINCT Customer_ID 
                FROM dbo.Enrollment_Status_Change AS Status 
                WHERE (Change_Date <= '12/31/2010')) 
GROUP BY 
    p.Tier

Also: most likely, your EXISTS check is wrong — since you didn't post your table structures, I can only guess — but my guess would be:

    AND EXISTS (SELECT * FROM dbo.Enrollment_Status_Change 
                WHERE Change_Date <= '12/31/2010' AND CustomerID = e.CustomerID)

Check for existence of any entries in dbo.Enrollment_Status_Change for the customer defined by e.CustomerID, with a Change_Date before that cut-off date. Right?

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

yes that is correct. in the enroll_status table there can be multiple records showing every time the active_status is changed.
0

Assuming you want to:

  • exclude all customers whose latest enrollment_status_change record was since the start of 2011 but

  • include all customers whose latest enrollment_status_change record was earlier than the end of 2010 (why else would you have put that EXISTS clause in?)

Then this should do it:

SELECT COUNT(e.Customer_ID) AS Total,
       p.Tier 
  FROM dbo.Phone_Tier p
  JOIN dbo.Enrollments e ON p.Phone_Model = e.Phone_Model
 WHERE dbo.Enrollments.Active_Status = 1
   AND e.Customer_ID NOT IN (
    SELECT Customer_ID
              FROM dbo.Enrollment_Status_Change status
             WHERE (Change_Date >= '2011-01-01')
   )
 GROUP BY p.Tier

Basically, the problem with your code is that joining a one-to-many table will always increase the row count. If you wanted to exclude all the records that had a matching row in the other table this would be fine -- you could just use a LEFT JOIN and then set a WHERE clause like Customer_ID IS NULL.

But because you want to exclude a subset of the enrollment_status_change table, you must use a subquery.

Your intention is not clear from the example given, but if you wanted to exclude anyone who's enrollment_status_change as before 2011, but include those who's status change was since 2011, you'd just swap the date comparator for <.

Is this any help?

Improve this answer

3 Comments

yeah that makes sense, and is exactly what I am trying to do. I apologize for my inability to accurately state it earlier. However when I try the above code it still gives me the additional record that was changed on 12/30/2010
maybe this will help a little. Basically anything changed before, or after, that date shouldn't be included in the results. The results are based on what is actually in the enrollments table on that specific date. So if something was changed to inactive before, or after, that date it would only be included in the resutls if it was active on 12/31/2010. Does that help?
Then change the Change_Date >= '2011-01-01' to Change_Date = '2010-12-30' ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.