PureConnect

Discussion Thread View

List users in their workgroup

  • 1.  List users in their workgroup

    Posted 08-24-2017 10:37
    I had a request to give a list of users and their workgroups. I don’t believe you can run a report in CIC for that but sql query can help The department is IT SQL and every groups have ITSQL keyword in it. For example, user “Bob Smith” in IT SQL department is in EOQV_ITSQL and EOSV_ITSQL_Expert This query will list every users in IT SQL department with a maximum of 4 workgroups WITH cte_AddRN AS ( SELECT mt.UserId, mt.WorkGroup, RN = ROW_NUMBER() OVER (PARTITION BY mt.UserId ORDER BY mt.WorkGroup) FROM CIC_PROD.dbo.UserWorkgroups mt (nolock) where workgroup like '%%ITSQL%%' ) SELECT arn.UserId ,I.FirstName ,I.LastName ,WorkGroup_1 = MAX(CASE WHEN arn.RN = 1 THEN arn.WorkGroup END) ,WorkGroup_2 = MAX(CASE WHEN arn.RN = 2 THEN arn.WorkGroup END) ,WorkGroup_3 = MAX(CASE WHEN arn.RN = 3 THEN arn.WorkGroup END) ,WorkGroup_4 = MAX(CASE WHEN arn.RN = 4 THEN arn.WorkGroup END) FROM cte_AddRN arn inner join CIC_PROD.dbo.Individual (nolock) I on I.ICUserID = arn.UserId inner join CIC_PROD.dbo.UserWorkgroups (nolock) w on w.UserId = arn.UserId GROUP BY arn.UserId ,I.FirstName ,I.LastName