CA SDM - MS SQL - Script to count member group

Document created by TMACUL Champion on Dec 24, 2015Last modified by TMACUL Champion on Feb 14, 2017
Version 3Show Document
  • View in full screen mode

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)

Attachments

    Outcomes