Please see if this does what you need. If so, might be able to clean it up a little:

SELECT

PersonId,

CASE WHEN NextCalMth...

Here is a different approach. Not sure if this will work better or worse than other methods, you'd just have to test it on your own data.

If you don't...

SELECT

ClientName,

MAX(CASE WHEN ContactType = 'Home' THEN ContactNumber ELSE 0 END) AS [HomeNumber],...

Why not one more variation 🙂 :

SELECT CASE WHEN Port_Value_Total >= 0 THEN Port_Value_Total ELSE 0 END AS Port_Value_Total

FROM (

SELECT SUM(CASE WHEN Port_ID = 3...