sql server - SQL Output Question -

edited

i running error , know happening can't see causing it. below sql code using. getting general results want, not accurately giving query correct 'where' clause.

if of assistance. count coming out this:

total       tier 1           high 2           low 

there 4 records in enrollment table. 3 active, , 1 not. 2 of records should displayed. 1 high, , 1 low. second low record in total flagged 'inactive' on 12/30/2010 , reflagged again on 1/12/2011 should not in results. changed initial '<=' '=' , results stayed same.

i need exclude record enrollments_status_change "active_status" changed 0 before date.

select count(dbo.enrollments.customer_id) total,        dbo.phone_tier.tier    dbo.phone_tier p   join dbo.enrollments eon p.phone_model = e.phone_model        (e.customer_id not in                (select customer_id                 dbo.enrollment_status_change status                 (change_date >'12/31/2010')))  group dbo.phone_tier.tier 

thanks assistance , apologize confusion. first time here , i'm trying correct etiquette on fly.

if don't want of fields table dbo.enrollment_status_change, , don't seem use in way — why include in joins? leave out.

plus: start using table aliases. hard read if use full table name in each join condition , clause.

your code should be:

select      count(e.customer_id) total, p.tier       dbo.phone_tier p inner join      dbo.enrollments e on p.phone_model = e.phone_model       e.active_status = 1      , exists (select distinct customer_id                  dbo.enrollment_status_change status                  (change_date <= '12/31/2010'))  group      p.tier 

also: likely, exists check wrong — since didn't post table structures, can guess — guess be:

    , exists (select * dbo.enrollment_status_change                  change_date <= '12/31/2010' , customerid = e.customerid)  

check existence of entries in dbo.enrollment_status_change customer defined e.customerid, change_date before cut-off date. right?