This will create a new field returned named ‘NewStatus’ and in it

Will be a 1, 0  or -1

select status,

         NewStatus =

case

when sf.status = 'PASS' then 1

when sf.status = 'FAIL' then 0

else -1

end

from smt_scorefile sf where examdate >= '9/1/2014'

This will return just PASS Records…

select status

from smt_scorefile sf where examdate >= '9/1/2014'

and

case

when sf.status = 'PASS' then 1

when sf.status = 'FAIL' then 0

else -1

end = 1