Basic Query using Outer Apply

SELECT DISTINCT B1_ALT_ID
, B1_PARCEL_NBR
, ATTRIB_VALUE_1
, ATTRIB_VALUE_2
, ATTRIB_VALUE_3
, CASE
when LEFT(isnull(ATTRIB_VALUE_2,''),1) = ''
then 'No Designation'
when LEFT(isnull(ATTRIB_VALUE_2,''),1) = '1'
then 'Residential'
else 'Commercial'
END AS 'parcel category'
,(CASE
WHEN B1P."B1_PER_TYPE" = '12FAM'
THEN 'RESIDENTIAL'
WHEN B1P."B1_PER_TYPE" = 'Other'
THEN 'COMMERCIAL'
ELSE 'NO DESIGNATION'
END) AS 'Comm or Res by group'
, (convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Total Estimated Costs'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Job Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Work Value'),0))) AS 'Total Estimated Cost'
 
 FROM dbo.B1PERMIT B1P

 OUTER APPLY (SELECT top 1 isnull(B1_PARCEL_NBR,'') AS 'B1_PARCEL_NBR'
FROM "dbo"."B3PARCEL" AS "B3PARCEL"
WHERE "B3PARCEL"."SERV_PROV_CODE"=B1P."SERV_PROV_CODE"
AND "B3PARCEL"."B1_PER_ID1"=B1P."B1_PER_ID1"
AND "B3PARCEL"."B1_PER_ID2"=B1P."B1_PER_ID2"
AND "B3PARCEL"."B1_PER_ID3"=B1P."B1_PER_ID3" ) AS A

 OUTER APPLY (SELECT top 1 isnull(ATTRIB_VALUE_1,'') AS 'ATTRIB_VALUE_1'
, isnull(ATTRIB_VALUE_2,'') AS 'ATTRIB_VALUE_2'
, isnull(ATTRIB_VALUE_3,'') AS 'ATTRIB_VALUE_3'
FROM "dbo"."Parcel_address" AS "Parcel_address"
WHERE "Parcel_address"."L1_PARCEL_NBR"=B1_PARCEL_NBR) AS B

 WHERE  (b1_file_dd >= @BeginDate)
AND (b1_file_dd < DATEADD(day,1,@EndDate))
and isnull(B1_APPL_CLASS, 'na') <> 'INCOMPLETE TMP'
and isnull(B1_APPL_CLASS, 'na') <> 'INCOMPLETE EST'

AND B1P."B1_PER_GROUP" = 'building'
AND b1_per_sub_type != 'health'
AND B1_ALT_ID not like '%vncy%'

AND B1P."REC_STATUS"='A'
--AND B1_PER_SUB_TYPE != 'Temporary Permit'
AND B1P.b1_alt_id not like '%tmp%'
--AND "B1PERMIT".B1_PER_TYPE <> 'C of I'
--AND "B1PERMIT".B1_PER_TYPE <> 'ZBA'
                AND B1P."B1_PER_TYPE"<>'complaint'
AND B1P."B1_APPL_STATUS" != 'Cancelled'
AND (convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Total Estimated Costs'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Job Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Cost'),0))
+ convert(float, isnull(dbo.fn_get_app_spec_info(B1P.SERV_PROV_CODE, B1P.B1_PER_ID1, B1P.B1_PER_ID2, B1P.B1_PER_ID3, 'Estimated Work Value'),0))
) >= @ATWHATVALUE