You could use CTEs instead of the temporary tables. However, you should check for any possible performance issues.
WITH MOVEMENTS AS(
SELECT *
FROM [GW_DW].[dbo].[DimStatusHistory] d
WHERE TransferFromToProgram <> ''
AND d.STATUS = 12
),
NEW_MOVEMENTS AS(
SELECT DISTINCT n.[CLT_NBR]
,n.[CHILD_NAME]
,n.[ReasonKey]
,n.[ReasonDesc]
,n.[EFFECT_DT]
,n.[Status]
,n.[STATUS_DESC]
,n.[DESCRIPT]
,n.[TRAN_TYPE]
,n.[OTYPE]
,n.[Old_FID]
,n.[NTYPE]
,n.[New_FID]
,n.[TransferFromToProgram]
,[ECMS].dbo.[FN_PRIOR_EFFECT_DT_FOR_STATUS](n.[EFFECT_DT], n.[CLT_NBR], 12) AS PRIOR_EFFECT_DT
,[ECMS].dbo.[FN_NEXT_EFFECT_DT_FOR_STATUS](n.[EFFECT_DT], n.[CLT_NBR], 12) AS FUTURE_EFFECT_DT
,n.[TOTAL_DAYS]
FROM [GW_DW].[dbo].[DimStatusHistory] n
LEFT OUTER JOIN MOVEMENTS m ON n.CLT_NBR = m.CLT_NBR
WHERE n.TransferFromToProgram NOT IN (
''
,'FBH - TFBH'
,'TFBH - FBH'
)
AND n.EFFECT_DT BETWEEN @from_dt AND @to_dt
AND n.COUNTY = 'NYC'
AND n.OTYPE NOT IN (
'RTC'
,'SLIP'
)
AND n.NTYPE NOT IN (
'RTC'
,'SLIP'
)
AND n.STATUS = 12
)
--Query 3
SELECT n.[CLT_NBR]
,n.[CHILD_NAME]
,c.CIN
,cl.DOB
,c.Age
,c.Sex
,c.PlcSource
,w.PLACED_DT AS APD
,w.IPD
,n.[ReasonKey]
,n.[ReasonDesc]
,n.[EFFECT_DT]
,n.[TransferFromToProgram]
,n.[OTYPE]
,n.[Old_FID]
,h1.Rsrc_Name AS Old_FP_Name
,h1.Orig_Cert AS Old_FP_Orig_Cert
,n.[NTYPE]
,n.[New_FID]
,h2.Rsrc_Name AS New_FP_Name
,h2.Orig_Cert AS New_FP_Orig_Cert
,n.PRIOR_EFFECT_DT
,m.Old_FID AS PRIOR_Old_FID
,n.FUTURE_EFFECT_DT
,f.New_FID AS FUTURE_New_FID
,c.STF_NBR
,c.Planner
,s.UNIT
,s.SupervisorName
,s.[SITE]
,s.DirectorName
,CASE WHEN (
n.NTYPE = 'KINS'
AND n.OTYPE <> 'KINS'
) THEN 1 WHEN n.ReasonKey IN (
'TE'
,'TJ'
) THEN 1 WHEN (
(n.New_FID = m.Old_FID)
AND (n.EFFECT_DT < DATEADD(D, 22, n.PRIOR_EFFECT_DT))
) THEN 1 ELSE 0 END AS POS_SCORE
,CASE WHEN (
n.NTYPE = 'KINS'
AND n.OTYPE <> 'KINS'
) THEN 0 WHEN n.ReasonKey IN (
'TE'
,'TJ'
) THEN 0 WHEN n.New_FID = m.Old_FID
AND n.EFFECT_DT < DATEADD(D, 22, n.PRIOR_EFFECT_DT) THEN 0 WHEN (
(n.Old_FID = f.New_FID)
AND (DATEADD(D, 22, n.EFFECT_DT) > n.FUTURE_EFFECT_DT)
) THEN 0 ELSE - 1 END AS NEG_SCORE
FROM NEW_MOVEMENTS n
LEFT OUTER JOIN MOVEMENTS m ON n.CLT_NBR = m.CLT_NBR
AND n.PRIOR_EFFECT_DT = m.EFFECT_DT
LEFT OUTER JOIN MOVEMENTS f ON n.CLT_NBR = f.CLT_NBR
AND n.FUTURE_EFFECT_DT = f.EFFECT_DT
LEFT OUTER JOIN GW_DW.dbo.DimClient c ON c.CLT_NBR = n.CLT_NBR
LEFT OUTER JOIN [ECMS].[dbo].[WFR_CLIENT] w ON w.CLT_NBR = n.CLT_NBR
LEFT OUTER JOIN [ECMS].[dbo].[CLIENT] cl ON cl.CLT_NBR = n.CLT_NBR
LEFT OUTER JOIN GW_DW.dbo.DimStaff s ON s.ECMS_Wrkr_ID = c.STF_NBR
LEFT OUTER JOIN [GW_DW].[dbo].[DimHome_FHD] h1 ON h1.Facility_ID = n.Old_FID
LEFT OUTER JOIN [GW_DW].[dbo].[DimHome_FHD] h2 ON h2.Facility_ID = n.New_FID
WHERE s.[SITE] <> 'CGS'
ORDER BY n.CHILD_NAME
,n.EFFECT_DT DESC