SQL Output Question

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开发者_JS百科 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.

---

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?

---

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?