开发者

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?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜