Use a cte, like so:
;with cte (newvalue) as (select COALESCE etc FROM sometable)
select case when newvalue < 0 then 0 else newvalue end as newvalue from cte
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills