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