Outer Applies For Various Fields

MOST RECENT INSPECTION DETAILS

OUTER APPLY (
    SELECT TOP 1 G6_ACT_TYP
,GA_LNAME
,G6_STATUS
,ISNULL(GA_FNAME, '') + CASE
WHEN ISNULL(GA_MNAME, '') = ''
THEN ''
ELSE CASE
WHEN ISNULL(GA_FNAME, '') = ''
THEN ISNULL(GA_MNAME, '')
ELSE ' ' + ISNULL(GA_MNAME, '')
END
END + CASE
WHEN ISNULL(GA_LNAME, '') = ''
THEN ''
ELSE CASE
WHEN ISNULL(GA_FNAME, '') + ISNULL(GA_MNAME, '') = ''
THEN ISNULL(GA_LNAME, '')
ELSE ' ' + ISNULL(GA_LNAME, '')
END
END AS 'Inspector Name'
,CONVERT(SMALLDATETIME, CASE
WHEN G6_STATUS = 'SCHEDULED'
THEN G6_ACT_DD
ELSE G6_COMPL_DD
END + 0, 101) AS 'MOST RECENT INSPECTION DATE'
,G6_ACT_NUM
,6_ACT_DES
,REC_FUL_NAM
FROM G6ACTION
WHERE REC_STATUS = 'A'
AND B1PERMIT.SERV_PROV_CODE = G6ACTION.SERV_PROV_CODE
AND B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
AND B1PERMIT.B1_PER_ID2 = G6ACTION.B1_PER_ID2
AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
ORDER BY CONVERT(SMALLDATETIME, CASE
WHEN G6_STATUS = 'SCHEDULED'
THEN G6_ACT_DD
ELSE G6_COMPL_DD
END + 0, 101) DESC
) AS C1

ALL INSPECTORS WHO ACTED ON RECORD

OUTER APPLY (
SELECT Inspectors = STUFF((
SELECT DISTINCT ',' + ISNULL([GA_FNAME], '') + CASE 
WHEN ISNULL([GA_MNAME], '') = ''
THEN ' '
ELSE ' ' + [GA_MNAME] + ' '
END + ISNULL([GA_LNAME], '')
FROM G6ACTION
WHERE B1PERMIT.SERV_PROV_CODE = G6ACTION.SERV_PROV_CODE
AND B1PERMIT.B1_PER_ID1 = G6ACTION.B1_PER_ID1
AND B1PERMIT.B1_PER_ID2 = G6ACTION.B1_PER_ID2
AND B1PERMIT.B1_PER_ID3 = G6ACTION.B1_PER_ID3
FOR XML PATH('')
), 1, 1, '')

) AS B1

GET ASSIGNEE

OUTER APPLY (
SELECT ISNULL([G3STAFFS].GA_FNAME, '') + CASE 
WHEN ISNULL([G3STAFFS].GA_MNAME, '') = ''
THEN ''
ELSE CASE 
WHEN ISNULL([G3STAFFS].GA_FNAME, '') = ''
THEN ISNULL([G3STAFFS].GA_MNAME, '')
ELSE ' ' + ISNULL([G3STAFFS].GA_MNAME, '')
END
END + CASE 
WHEN ISNULL([G3STAFFS].GA_LNAME, '') = ''
THEN ''
ELSE CASE 
WHEN ISNULL([G3STAFFS].GA_FNAME, '') + ISNULL([G3STAFFS].GA_MNAME, '') = ''
THEN ISNULL([G3STAFFS].GA_LNAME, '')
ELSE ' ' + ISNULL([G3STAFFS].GA_LNAME, '')
END
END AS 'Assignee Name'

  FROM BPERMIT_DETAIL INNER JOIN [G3STAFFS] ON 
    BPERMIT_DETAIL.[B1_ASGN_STAFF] = [G3STAFFS].[USER_NAME]
  WHERE B1PERMIT.SERV_PROV_CODE = BPERMIT_DETAIL.SERV_PROV_CODE
AND B1PERMIT.B1_PER_ID1 = BPERMIT_DETAIL.B1_PER_ID1
AND B1PERMIT.B1_PER_ID2 = BPERMIT_DETAIL.B1_PER_ID2
AND B1PERMIT.B1_PER_ID3 = BPERMIT_DETAIL.B1_PER_ID3

) AS A3 NEIGHBORHOOD

OUTER APPLY ( SELECT TOP 1 L1_ATTRIBUTE_VALUE AS 'Neighborhood' FROM ACCELAPROD.dbo.B3PARCEL b3pa LEFT OUTER JOIN ACCELAPROD.dbo.L3APO_ATTRIBUTE ON L3APO_ATTRIBUTE.L1_APO_NBR = [ACCELAPROD].dbo.FN_GET_PARCEL_NBR (B1P."SERV_PROV_CODE", B1P."B1_PER_ID1", B1P."B1_PER_ID2", B1P."B1_PER_ID3") WHERE L1_ATTRIBUTE_NAME = 'planning neighborhood' ) AS A