CA Service Management

  • Private Community
 View Only
Back to Library

CA SDM - MS SQL - Script to count member group 

Dec 24, 2015 09:49 AM

CA SDM - MS SQL - Script to count member group

 

 

select gc.contact_uuid,supc.last_name + ', ' + supc.first_name as Group_Mgr,
case when gc.last_name is null then 'Analysts not in a group' else gc.last_name end as group_name,
count(*)
from mdb.dbo.ca_contact c with (nolock) -- c is the contact record for the group member
left outer join mdb.dbo.grpmem g with (nolock) on g.member = c.contact_uuid -- join contact to find groups they belong to
left outer join mdb.dbo.ca_contact gc with (nolock) on g.group_id = gc.contact_uuid -- gc is the contact record for the grup - join to get the group name
left outer join mdb.dbo.ca_contact supc with (nolock) on gc.supervisor_contact_uuid = supc.contact_uuid --- supc is the contact record for the group manager/supervisor
where
c.contact_type = 2307 and
c.inactive = 0 and --- active contacts (group members)
gc.inactive = 0 --- active groups
group by
gc.contact_uuid,supc.last_name + ', ' + supc.first_name ,
case when gc.last_name is null then 'Analysts not in a group' else gc.last_name end
order by 2 -- sort by group manager last name

By  Alexis Osborne 

 

use mdb
go

set nocount on
declare
  @tb table
  (
  group_id binary(16),
  last_name nvarchar(200),
  qtde int default 0
  )

declare
  @qtde table
  (
  group_id binary(16),
  qtde int
  )

insert into @tb (group_id, last_name)
select contact_uuid, last_name
from ca_contact
where contact_type =  2308

insert into @qtde (group_id, qtde)
select group_id, count(0)
from grpmem
group by group_id

update @tb
set qtde = b.qtde
from @tb a
inner join @qtde b on a.group_id = b.group_id

set nocount off
select * 
from @tb
order by qtde desc

 

table (Transact-SQL)

Statistics
0 Favorited
4 Views
0 Files
0 Shares
0 Downloads

Tags and Keywords

Comments

wdrain
Feb 26, 2018 04:51 PM

thanks for contribution... Here's another sql version with group names, manager and member count...

 

select
g.last_name as [ServiceDesk Group], isnull(mgr.Manager,'') as Manager, [# of Members]
from ca_contact g
left join ( -- group member count
select group_id, count(*) [# of Members] from grpmem
group by group_id
) gc on gc.group_id = g.contact_uuid

left join ( -- group's manager
select group_id, m.first_name + ' ' + last_name as [Manager]
from grpmem gm
join ca_contact m on m.contact_uuid = gm.member
where manager_flag = 1
) mgr on mgr.group_id = gc.group_id

where contact_type = 2308 and inactive = 0 --filter for active Groups
order by g.last_name

TMACUL
Feb 14, 2017 06:33 AM

HI Alexis Osborne,

 

  Thank you for sharing with us, i put on top your script.

 

Regards

Legacy User
Feb 13, 2017 08:17 AM

Try this.   It's a bit easier than a script and returns the group manager/supervisor name.  

 

---======================================================================================================================================

--- Assignment Groups: List Active Groups owned by Managers (supervisor) - list group uuid, mgr name, group name, # of active members

---======================================================================================================================================

select gc.contact_uuid,supc.last_name + ', ' + supc.first_name as Group_Mgr,

case when gc.last_name is null then 'Analysts not in a group' else gc.last_name end as group_name,

count(*)

from mdb.dbo.ca_contact c with (nolock) -- c is the contact record for the group member

left outer join mdb.dbo.grpmem g with (nolock) on g.member = c.contact_uuid -- join contact to find groups they belong to

left outer join mdb.dbo.ca_contact gc with (nolock) on g.group_id = gc.contact_uuid -- gc is the contact record for the grup - join to get the group name

left outer join mdb.dbo.ca_contact supc with (nolock) on gc.supervisor_contact_uuid = supc.contact_uuid --- supc is the contact record for the group manager/supervisor

where

c.contact_type = 2307 and

c.inactive = 0 and --- active contacts (group members)

gc.inactive = 0 --- active groups

group by

gc.contact_uuid,supc.last_name + ', ' + supc.first_name ,

case when gc.last_name is null then 'Analysts not in a group' else gc.last_name end

order by 2 -- sort by group manager last name

Related Entries and Links

No Related Resource entered.