-- One way is to join the transcript table twice and switch from isNull() to coalesce().

select distinct

p.first_name

,p.last_name

,'class level' = case

when a.credits + coalesce(t2.earned_credits, t1.earned_credits, 0) between 1 and 32 and a.program ='ft'then 'F1'

when a.credits + coalesce(t2.earned_credits, t1.earned_credits, 0) between 33 and 61 and a.program ='ft' then 'F2'

when a.credits + coalesce(t2.earned_credits, t1.earned_credits, 0) >= 62 and a.program ='ft'then 'F3'

when a.credits + coalesce(t2.earned_credits, t1.earned_credits, 0) between 1 and 25 and a.program ='pt'then 'P1'

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

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

when a.credits + coalesce(t2.earned_credits, t1.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 t2

on p.people_code_id=t2.people_code_id

and t2.record_type='O'

and t2.academic_term='Fall'

and t2.academic_year='2007'

and t2.seqno = '002'

left outer join transcriptgpa as t1

on p.people_code_id=t1.people_code_id

and t1.record_type='O'

and t1.academic_term='Fall'

and t1.academic_year='2007'

and t1.seqno = '001'

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'