Inactive Members in Groups.sql

-- Inactive Group Members
SELECT (SELECT COUNT(*) FROM ENGINE.GROUP_MEMBERS GM WHERE GM.USER_ID=GRP.USER_ID) AS ActiveCount,
GRP.USER_ID AS GroupID,
GRP.DISPLAY_NAME AS GroupName,
GRP.USER_NAME AS GroupDesignName,
BOOLEAN_VALUES.DESCRIPTION IsTrainingGroup,
MEMBERS.USER_ID AS InactiveUserID,
MEMBERS.DISPLAY_NAME AS InactiveUser,
MEMBERS.USER_NAME AS InactiveUserName,
USER_LOCATION_PROFILE.DISPLAY_NAME AS PrimaryLoc,
MEMBERS.ETQ$MODIFIED_DATE AS UserLastModified
FROM ENGINE.USER_SETTINGS MEMBERS
LEFT JOIN ENGINE.INACTIVATED_MEMBERS AS INACTIVATED_MEMBERS ON (INACTIVATED_MEMBERS.INACTIVATED_MEMBER_ID = MEMBERS.USER_ID)
LEFT JOIN ENGINE.USER_SETTINGS AS GRP ON (GRP.USER_ID = INACTIVATED_MEMBERS.USER_ID)
LEFT JOIN DATACENTER.LOCATION_PROFILE USER_LOCATION_PROFILE ON (MEMBERS.PRIMARY_LOCATION_ID = USER_LOCATION_PROFILE.LOCATION_PROFILE_ID)
LEFT JOIN ENGINE.BOOLEAN_VALUES BOOLEAN_VALUES ON (BOOLEAN_VALUES.VALUE =GRP.IS_TRAINING_GROUP)
WHERE GRP.USER_NAME IS NOT NULL
  AND GRP.IS_INACTIVE=0
ORDER BY 1, GRP.DISPLAY_NAME, MEMBERS.DISPLAY_NAME