List all Active groups with no members or zero members SQL query

book

Article ID: 101601

calendar_today

Updated On:

Products

SUPPORT AUTOMATION- SERVER CA Service Desk Manager - Unified Self Service KNOWLEDGE TOOLS CA Service Management - Asset Portfolio Management CA Service Management - Service Desk Manager

Issue/Introduction



How do list all Service Desk active Groups with no members or zero members in SQL query? 

Environment

Release:
Component: USRD

Resolution

In SQL Studio below query would return all Service Desk active groups with no members.

SELECT CNT.last_name AS GroupName,COUNT(MEM.id) AS COUNT FROM MDB.dbo.grpmem MEM WITH (NOLOCK) FULL OUTER JOIN mdb.dbo.ca_contact CNT WITH (NOLOCK) ON MEM.group_id = CNT.contact_uuid FULL OUTER JOIN mdb.dbo.ca_contact CNT1 WITH (NOLOCK) ON MEM.member = CNT1.contact_uuid WHERE CNT.contact_type = '2308' AND CNT.inactive = 0 AND MEM.member IS NULL GROUP BY CNT.last_name ORDER BY COUNT ASC