oliveraustin (1/28/2014)
Ah ha, thought how to reduce the problem size down to 12 groups 🙂Since the Roles with multiple groups consist of<=12 groups and the rest of the all I need to to do is filter the LDAP data and reduce it those entries that are in the set of groups that are part of multi group roles!...yippeee
Original problem is
A role consists of between 1 and x groups from a set of x groups.
A role can only have a particular group once.
A user may have more than one role
Example:
Role | Group
Helpdesk | Password Reset
Security | Password Reset,
Security | Enable User
Reception | Enable User
Reception | Disable User
The data I have is of the form
User | Group
SmithA | Password Reset
SmithA | Enable User
BloggsJ | Password Reset
MouseM | Enable User
MouseM | Disable User
From that I need to deduce the users have the following roles
User | Role
SmithA | Security
BloggsJ | Helpdesk
MouseM | Reception
Thanks
I might be barking up the wrong tree completely here, but this may do what you want
with RoleGroup as (
SELECT *
FROM (VALUES
('Helpdesk' , 'Password Reset'),
('Security' , 'Password Reset'),
('Security', 'Enable User'),
('Reception' , 'Enable User'),
('Reception' , 'Disable User')
) rg(Role, Grp)
),
UserGroup as (
SELECT *
FROM (VALUES
('SmithA' , 'Password Reset'),
('SmithA' , 'Enable User'),
('BloggsJ' , 'Password Reset'),
('MouseM' , 'Enable User'),
('MouseM' , 'Disable User')
) ug(Usr, Grp)
),
potentialRoles AS (
SELECT usr, role, ug.grp
FROM (SELECT usr, grp, COUNT(*) OVER (PARTITION BY usr) gCount, ROW_NUMBER() OVER (PARTITION BY Usr ORDER BY grp) N FROM UserGroup) ug
INNER JOIN (SELECT role, grp, COUNT(*) OVER (PARTITION BY role) gCount, ROW_NUMBER() OVER (PARTITION BY role ORDER BY grp) N FROM RoleGroup) rg
ON ug.grp = rg.grp and ug.N = rg.N and ug.gCount = rg.gCount
)
SELECT usr, role
FROM potentialRoles
GROUP BY usr, role;