Or, how about this? The cte does the grouping like a temp table, but without any logging overhead.

;with cte as (

SELECT d.name,

COUNT(DISTINCT a.ID) AS DistinctIDs,

SUM(DISTINCT c.number_claims) AS number_claims,

COUNT(DISTINCT c.filename) AS number_files

FROM TableOne AS a

INNER JOIN TableTwo AS b with (Nolock)

ON a.claim_id = b.id

INNER JOIN TableThree AS c with (Nolock)

ON b.transaction_id = c.id

INNER JOIN TableFour AS d with (Nolock)

ON c.provider_id = d.id

GROUP BY d.name,a.procedure_code

)

select d.name,

CASE WHEN a.procedure_code LIKE '%F' THEN DistinctIDs ELSE 0 END AS NumberOf_Code,

number_claims,number_files

from cte