Try using a case statement:

select

Total_within_target = sum(case when Wait_Status.Value="Within Target"

then Fields!Surgical_Case_Count.Value

else 0

end),

Total_missing = sum(case when Wait_Status.Value="Missing"

then Fields!Surgical_Case_Count.Value

else 0

end),

Total_above_target = sum(case when Wait_Status.Value="above_target"

then Fields!Surgical_Case_Count.Value

else 0

end)

from ...