EMSE Snippets

Apply An Address Condtion (You have to create any conditions used in scripts in the system or you won't have access to be able to shut them off when they are no longer needed)

true ^ addAddressCondition(null, "Address", "Not Satisfied", "Vacant or Foreclosing","Address is either Vacant or in the process of Foreclosure.  From " + capId,"Lock")

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

Query Results as List

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