When using ...coalesce(t2.earned_credits, t1.earned_credits, 0) ...

IF, t2 or t1 do not return any rows, its going to use 0 for the aggregate function instead of a null?

A: Yes. That's...

-- try this if you do not have a large number of rows

create table Display (itemID int, imageID int, catName varchar(10))

create unique index ix_tmp on Display (catName) with ignore_dup_key

------

truncate table...

-- 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...

Add IsNull() or Coalesce() around the transcription credits, like this:

select distinct p.first_name

,p.last_name

,'class level' = case when a.credits + t.earned_credits between 1 and 32 and a.program ='ft'then 'F1'

when a.credits +...

See

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm

Which is SQL Server 2005 Books Online for

sp_dbcmptlevel (Transact-SQL)

and Lists Behavioral Differences Between Level 60 or 65 and Level 70, 80, or 90