SELECT M.GROUP_ID, GROUP_NAME, M.RESOURCE_ID, M.PERSON_ID, max(r.START_DATE_ACTIVE) START_DATE_ACTIVE ,max(R.END_DATE_ACTIVE) END_DATE_ACTIVE, count(*) cnt FROM JTF_RS_ROLE_RELATIONS R, JTF_RS_GROUP_MEMBERS M, JTF_RS_GROUPS_TL T, JTF_RS_ROLES_B ROLE WHERE ROLE.MANAGER_FLAG = 'Y' AND R.ROLE_ID = ROLE.ROLE_ID AND R.ROLE_RESOURCE_TYPE = 'RS_GROUP_MEMBER' AND M.GROUP_MEMBER_ID = R.ROLE_RESOURCE_ID AND R.DELETE_FLAG <> 'Y' AND M.DELETE_FLAG <> 'Y' AND ROLE.ROLE_TYPE_CODE in ('SALES','TELESALES','FIELDSALES','PRM') AND ROLE.ACTIVE_FLAG = 'Y' AND M.GROUP_ID = T.GROUP_ID AND T.LANGUAGE = USERENV('LANG') AND M.GROUP_ID IS NOT NULL group by M.GROUP_ID, GROUP_NAME, M.RESOURCE_ID, M.PERSON_ID