One more thing i need to add...On their transcript gpa record, when it is pulling t.earned credits, their is also a trans seq number of either 001 or 002. Not everyone has a 002 record, BUT, if they do i need to use that record, if they dont have a trans seq of 002, then i need to use their 001 record. Is it possible to incoporate this into my code? This is what i have thus far, which is pulling everyone correctly, BUT, some people are appearing twice because they have both a trans seq 001 and 002 record, it should only be pulling their 002 record if they have it, if not, then 001.

select distinct p.first_name

,p.last_name

,'class level' = case when a.credits + isnull(t.earned_credits,0) between 1 and 32 and a.program ='ft'then 'F1' when a.credits + isnull(t.earned_credits, 0) between 33 and 61 and a.program ='ft' then 'F2'when a.credits + isnull(t.earned_credits, 0) >= 62 and a.program ='ft'then 'F3' when a.credits + isnull(t.earned_credits, 0) between 1 and 25 and a.program ='pt'then 'P1'

when a.credits + isnull(t.earned_credits, 0) between 26 and 47 and a.program ='pt'then 'P2'

when a.credits + isnull(t.earned_credits, 0) between 48 and 70 and a.program ='pt'then 'P3'

when a.credits + isnull(t.earned_credits, 0) >= 71 and a.program ='pt'then 'P4'

end

from people as p

inner join academic as a

on p.people_code_id=a.people_code_id

left outer join transcriptgpa as t

on p.people_code_id=t.people_code_id

and t.record_type='O'and t.academic_term='Fall'--records in the transcript gpa table

and t.academic_year='2007'

where a.academic_term='Spring'

and a.academic_year='2008'

and a.enroll_separation ='enrl'

and a.academic_session='main'

and (a.program ='ft' or a.program='pt')

order by 'class level'