SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL to find the biggest role
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

olly
Starting Member

5 Posts

Posted - 07/11/2014 :  04:36:03  Show Profile  Reply with Quote
Hi,
We've got a system where users select a role from the service catalogue and the role is mapped to membership of some groups in the target system. There is overlap of the groups across some roles and some roles are fulfilled by a group set that is a complete subset of another role.
It's the latter that's causing me a problem. I don't know how to extract the super set only.

Role Grps
Role1 GrpA
Role1 GrpB
Role1 GrpC
Role2 GrpA
Role2 GrpB
Role3 GrpA
Role3 GrpD

Usr Grps
User1 GrpA
User1 GrpB
User2 GrpA
User2 GrpB
User2 GrpC
User3 GrpA
User3 GrpD
User4 GrpD

Output required
Usr Role
User1 Role2
User2 Role1
User3 Role3

Rather than
Usr Role
User1 Role2
User2 Role1
User2 Role2
User3 Role3

Thanks for your help

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 07/11/2014 :  11:25:04  Show Profile  Reply with Quote
Here is a guess. If my guess is not right, can you please define the logic you use to get your expected output in words?
DECLARE @RoleGroup TABLE (Role VARCHAR(20), Grp VARCHAR(20))
INSERT @RoleGroup VALUES
('Role1', 'GrpA'),
('Role1', 'GrpB'),
('Role1', 'GrpC'),
('Role2', 'GrpA'),
('Role2', 'GrpB'),
('Role3', 'GrpA'),
('Role3', 'GrpD')

DECLARE @UserGroup TABLE (Usr VARCHAR(20), Grp VARCHAR(20))
INSERT @UserGroup VALUES

('User1', 'GrpA'),
('User1', 'GrpB'),
('User2', 'GrpA'),
('User2', 'GrpB'),
('User2', 'GrpC'),
('User3', 'GrpA'),
('User3', 'GrpD'),
('User4', 'GrpD')



SELECT 
	Usr, 
	Role
FROM
	(
		SELECT
			*
			,ROW_NUMBER() OVER (PARTITION BY Usr ORDER BY GrpCount DESC, Role DESC) AS RowNum
		FROM
			(
				SELECT 	
					UG.Usr,
					RG.Role,
					COUNT(*) AS GrpCount
				FROM 
					@UserGroup AS UG
				INNER JOIN 
					@RoleGroup AS RG
					ON UG.Grp = RG.Grp
				GROUP BY
					UG.Usr,
					RG.Role
			) AS T
	) AS T1
WHERE
	RowNum = 1
	
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 07/11/2014 :  13:28:32  Show Profile  Reply with Quote
This may not be the most efficient way, but I believe it's accurate:


SELECT rg.Role, ug.Usr, COUNT(ug.Grp) AS Group_Count
FROM @UserGroup ug
INNER JOIN @RoleGroup rg ON
    rg.Grp = ug.Grp
LEFT OUTER JOIN (
    SELECT Role, COUNT(Grp) AS Grp_Count
    FROM @RoleGroup
    GROUP BY Role
) AS rg_totals ON
    rg_totals.Role = rg.Role
LEFT OUTER JOIN (
    SELECT Usr, COUNT(Grp) AS Grp_Count
    FROM @UserGroup
    GROUP BY Usr
) AS ug_totals ON
    ug_totals.Usr = ug.Usr
GROUP BY
    ug.Usr,
    rg.Role
HAVING
    MAX(rg_totals.Grp_Count) = MAX(ug_totals.Grp_Count) AND
    COUNT(ug.Grp) = MAX(ug_totals.Grp_Count)
ORDER BY Role, Usr

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 07/11/2014 :  15:24:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Basically the same as Lamprey's
WITH cteSource(Usr, [Role], rn, cnt)
AS (
	SELECT		u.Usr,
			r.[Role],
			ROW_NUMBER() OVER (PARTITION BY Usr ORDER BY COUNT(*) DESC, [Role] DESC) AS rn,
			COUNT(*) OVER (PARTITION BY Usr) AS cnt
	FROM		@UserGroup AS u
	INNER JOIN	@RoleGroup AS r ON r.Grp = u.Grp
	GROUP BY	u.Usr,
			r.[Role]
)
SELECT	Usr,
	[Role]
FROM	cteSource
WHERE	rn = 1
	AND cnt >= 2;



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 07/11/2014 :  18:31:11  Show Profile  Reply with Quote
With the same core logic difference.

If you add these rows to the User table:
('User5', 'GrpA'),
('User5', 'GrpB'),
('User5', 'GrpC'),
('User5', 'GrpD')
then check the results, you'll see what I mean.

I'm not sure which method olly considers more accurate.
Go to Top of Page

olly
Starting Member

5 Posts

Posted - 07/12/2014 :  02:24:54  Show Profile  Reply with Quote
Hi,
Thanks for the replies guys, I'll look into them on Monday when I'm back at work.
I didn't mention this is in MS Access so I can implement ScottPlatcher's reply but probably not the other.
I'll try that in SQL server too though just to get my head round it.
My gut/totally off-the-cuff feel is that it's a harder problem than you think and that maybe I haven't given enough data or examples but as I said it'll have to wait till Monday now.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000