Replace

LEFT JOIN (

SELECT

msp_flag,

bc_code

FROM

dbo.bs_contract(nolock)

GROUP BY

msp_flag,

bc_code

) F ON F.bc_code = C.comp_code

AND F.msp_flag = 1

with

LEFT JOIN dbo.bs_contract F (nolock)

ON F.bc_code = C.comp_code AND F.msp_flag = 1

Probably wont make any performance difference