Groups with No Members.sql

-- Groups with no members
SELECT G.USER_ID AS GROUP_ID, G.DISPLAY_NAME AS GROUP_NAME, G.USER_NAME AS GROUP_DESIGN_NAME, BOOLEAN_VALUES.DESCRIPTION AS IS_TRAINING_GROUP, IM.DISPLAY_NAME AS INACTIVE_USER, INACTIVE_USER_LOC.DISPLAY_NAME AS PRIMARY_LOC
--, G.ETQ$CREATED_DATE, A.DISPLAY_NAME AS AUTHOR, G.ETQ$MODIFIED_DATE, E.DISPLAY_NAME AS LAST_EDITOR
FROM ENGINE.USER_SETTINGS G
LEFT JOIN ENGINE.INACTIVATED_MEMBERS I ON G.USER_ID=I.USER_ID
LEFT JOIN ENGINE.USER_SETTINGS IM ON I.INACTIVATED_MEMBER_ID=IM.USER_ID
LEFT JOIN DATACENTER.LOCATION_PROFILE INACTIVE_USER_LOC ON (IM.PRIMARY_LOCATION_ID = INACTIVE_USER_LOC.LOCATION_PROFILE_ID)
LEFT JOIN ENGINE.USER_SETTINGS A ON G.ETQ$AUTHOR=A.USER_ID
LEFT JOIN ENGINE.USER_SETTINGS E ON G.ETQ$LAST_EDITOR=E.USER_ID
LEFT JOIN ENGINE.BOOLEAN_VALUES BOOLEAN_VALUES ON (BOOLEAN_VALUES.VALUE =G.IS_TRAINING_GROUP)
WHERE G.IS_GROUP=1
  AND G.IS_INACTIVE=0
  AND G.DISPLAY_NAME NOT LIKE '%DESIGNER%'
  AND G.DISPLAY_NAME NOT LIKE '%READER%'
  AND G.DISPLAY_NAME NOT LIKE '%AUTHOR%'
  AND G.DISPLAY_NAME NOT LIKE '%MANAGER%'
  AND G.DISPLAY_NAME <> 'Depositor'
  AND G.DISPLAY_NAME <> 'Applicability Reviewers'
  AND NOT EXISTS (SELECT 'X'
				  FROM ENGINE.GROUP_MEMBERS GM
				  WHERE GM.USER_ID=G.USER_ID)
ORDER BY G.DISPLAY_NAME, IM.DISPLAY_NAME