May 19, 2011 at 10:39 am
I have a table that includes user IDs and permissions for people within a department.
I'm trying to find out what appliction+mnemonic+security is common to ALL people within a department so that I can group security together in a 'base' group. If one person in the department doesn't have the exact same security as all the others for a mnemonic, then it shouldn't be included. Another way to say it is, I want to get all of the people in a department and see what application+mnemonic+security combination is common to ALL of them. Does anyone know how I might approach this?
Here's an example:
CREATE TABLE [dbo].[Permissions](
[UserID] [varchar](50) NULL,
[Application] [varchar](50) NULL,
[Mnemonic] [varchar](50) NULL,
[DoOnlyThese] [varchar](1) NULL,
[NeverDoThese] [varchar](1) NULL,
[InquiryOnly] [varchar](1) NULL,
[Privileged] [varchar](1) NULL,
[Department] [varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Permissions] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Mike', N'ST', N'ABC', N'X', NULL, N'X', NULL, N'Sales')
INSERT [dbo].[Permissions] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Mike', N'ST', N'DEF', N'X', NULL, NULL, NULL, N'Sales')
INSERT [dbo].[Permissions] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'John', N'ST', N'ABC', N'X', NULL, N'X', NULL, N'Sales')
INSERT [dbo].[Permissions] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'John', N'ST', N'DEF', N'X', NULL, NULL, N'X', N'Sales')
INSERT [dbo].[Permissions] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Mary', N'ST', N'ABC', N'X', NULL, N'X', NULL, N'Sales')
INSERT [dbo].[Permissions] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Mary', N'ST', N'HIJ', NULL, N'X', NULL, NULL, N'Sales')
INSERT [dbo].[Permissions] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Mary', N'ST', N'DEF', N'X', NULL, NULL, N'X', N'Sales')
INSERT [dbo].[Permissions] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Joe', N'ST', N'ABC', N'X', NULL, N'X', NULL, N'Sales')
SELECT * FROM Permissions
The ideal result would be something like:
CREATE TABLE [dbo].[Result](
[UserID] [varchar](50) NULL,
[Application] [varchar](50) NULL,
[Mnemonic] [varchar](50) NULL,
[DoOnlyThese] [varchar](1) NULL,
[NeverDoThese] [varchar](1) NULL,
[InquiryOnly] [varchar](1) NULL,
[Privileged] [varchar](1) NULL,
[Department] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[Result] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Base', N'ST', N'ABC', N'X', NULL, N'X', NULL, N'Sales')
INSERT [dbo].[Result] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Mike', N'ST', N'DEF', N'X', NULL, NULL, NULL, N'Sales')
INSERT [dbo].[Result] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'John', N'ST', N'DEF', N'X', NULL, NULL, N'X', N'Sales')
INSERT [dbo].[Result] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Mary', N'ST', N'HIJ', NULL, N'X', NULL, NULL, N'Sales')
INSERT [dbo].[Result] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Mary', N'ST', N'DEF', N'X', NULL, NULL, N'X', N'Sales')
INSERT [dbo].[Result] ([UserID], [Application], [Mnemonic], [DoOnlyThese], [NeverDoThese], [InquiryOnly], [Privileged], [Department]) VALUES (N'Joe', N'ST', N'CoveredInBase', NULL, NULL, NULL, NULL, N'Sales')
SELECT * FROM Result
Thanks for any help!
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 19, 2011 at 11:41 am
Awesome way of asking a question, Mike 🙂 Thanks for that...
This might help you:
; WITH DistUserCnt AS
(
SELECT Department, COUNT(DISTINCT UserID) DistUsrCnt
FROM Permissions
GROUP BY Department
)
, BaseGrps AS
(
SELECT 'Base' UserID, Application , Mnemonic , DoOnlyThese , NeverDoThese , InquiryOnly
, Privileged , Department
FROM Permissions SrcTable
GROUP BY Application , Mnemonic , DoOnlyThese , NeverDoThese , InquiryOnly
, Privileged , Department
HAVING COUNT( DISTINCT UserID) = ( SELECT DistUsrCnt
FROM DistUserCnt
WHERE DistUserCnt.Department = SrcTable.Department )
)
SELECT * FROM BaseGrps
UNION ALL
SELECT CrsApp.*
FROM BaseGrps BG
CROSS APPLY Permissions CrsApp
WHERE BG.Application <> CrsApp.Application
OR BG.Mnemonic <> CrsApp.Mnemonic
OR BG.DoOnlyThese <> CrsApp.DoOnlyThese
OR BG.NeverDoThese <> CrsApp.NeverDoThese
OR BG.Privileged <> CrsApp.Privileged
OR BG.InquiryOnly <> CrsApp.InquiryOnly
AND ( BG.Department = CrsApp.Department OR CrsApp.Department IS NULL )
May 19, 2011 at 12:12 pm
ColdCoffee,
Thanks for the response. Unfortunately, the query you wrote doesn't return any rows. I wonder if there's something going on with the HAVING clause.
Mike Scalise, PMP
https://www.michaelscalise.com
May 19, 2011 at 12:16 pm
For the test data you provided, i get the same results... can u please run it again and check?
May 19, 2011 at 2:45 pm
ColdCoffee,
Yes, it works. I must have been doing something a little different. Anyway, the one piece I think it's missing is a line that has Joe, ST, CoveredByBase, then all nulls for security. Is there a way to incorporate that?
Thanks,
Mike
Mike Scalise, PMP
https://www.michaelscalise.com
May 19, 2011 at 3:10 pm
mikes84 (5/19/2011)
ColdCoffee,Anyway, the one piece I think it's missing is a line that has Joe, ST, CoveredByBase, then all nulls for security. Is there a way to incorporate that?
Yeah, i regressed the code and i found that's missing.. a tweak in the WHERE clause should pull that as well.. i'm in the middle of something now.. will give u an update in 2 hrs or so...
May 19, 2011 at 8:24 pm
It's interesting, I don't have a solution for you, but when you insert something you actually don't need to write so many insert statements
You can just write one statement
insert into dbo.tb(col1,col2)
values(a1,a2),
values(a2,a3),
.....
May 20, 2011 at 6:43 am
Thanks, John. The reason for so many INSERT statements is that I scripted the table using SSMS, so it, by default, prints an insert for each row to be added to the table.
Mike Scalise, PMP
https://www.michaelscalise.com
May 20, 2011 at 11:28 am
As promised, here is the solution to it:
; WITH DistUserCnt AS
(
--== Get the distinct user count per department
SELECT Department, COUNT(DISTINCT UserID) DistUsrCnt
FROM Permissions
GROUP BY Department
)
, BaseGrps AS
(
/*
The GROUP BY will find the different combinations of "Security" columns per department
HAVING COUNT( DISTINCT UserID) will get the count of DISTINCT UserID per combination
The Sub-Query (DistUserCnt) will then provide this HAVING clause with DISTINCT User Count per dept.
Now if a combination is present for all users in a dept, the HAVING and SubQuery counts will match
We are capturing only those combinations that match in counts and are substitutinng BASE for UserID
*/
SELECT UserID = 'Base' + CAST ( ROW_NUMBER() OVER( ORDER BY ( SELECT NULL)) AS VARCHAR(19))
, Application , Mnemonic , DoOnlyThese , NeverDoThese , InquiryOnly
, Privileged , Department
FROM Permissions SrcTable
GROUP BY Application , Mnemonic , DoOnlyThese , NeverDoThese , InquiryOnly
, Privileged , Department
HAVING COUNT( DISTINCT UserID) = ( SELECT DistUsrCnt
FROM DistUserCnt
WHERE DistUserCnt.Department = SrcTable.Department )
)
, UsersSecurityOtherThanBaseGrps AS
(
--== Gathering the combinations that DONT match combinations in a Base Groups per UserID
SELECT CrsApp.*
FROM BaseGrps BG
CROSS APPLY Permissions CrsApp
WHERE (
--== This will get all the rows for each UserID that are not having
-- the combinations of a Base Group
ISNULL ( BG.Application , 'A') <> ISNULL ( CrsApp.Application , 'A')
OR ISNULL ( BG.Mnemonic , 'A') <> ISNULL ( CrsApp.Mnemonic , 'A')
OR ISNULL ( BG.DoOnlyThese , 'A') <> ISNULL ( CrsApp.DoOnlyThese , 'A')
OR ISNULL ( BG.NeverDoThese , 'A') <> ISNULL ( CrsApp.NeverDoThese , 'A')
OR ISNULL ( BG.Privileged , 'A') <> ISNULL ( CrsApp.Privileged , 'A')
OR ISNULL ( BG.InquiryOnly , 'A') <> ISNULL ( CrsApp.InquiryOnly , 'A')
AND ISNULL ( BG.Department , 'A') = ISNULL ( CrsApp.Department , 'A')
)
)
, SingleUsersWithBaseGrps AS
(
--== Gathering User List with ONLY a Base Group combination
SELECT CrsApp.UserID ,
CrsApp.Application ,
'Covered In: Base'+ REPLACE(BG.UserID ,'Base','') AS Mnemonic,
NULL AS DoOnlyThese,
NULL AS NeverDoThes,
NULL AS Privileged ,
NULL AS InquiryOnly,
NULL AS Department
FROM BaseGrps BG
CROSS APPLY Permissions CrsApp
WHERE (
--== This branch will get all the rows for each UserID that are having
-- the combinations of a Base Group but present only once.
ISNULL ( BG.Application , 'A') = ISNULL ( CrsApp.Application , 'A')
AND ISNULL ( BG.Mnemonic , 'A') = ISNULL ( CrsApp.Mnemonic , 'A')
AND ISNULL ( BG.DoOnlyThese , 'A') = ISNULL ( CrsApp.DoOnlyThese , 'A')
AND ISNULL ( BG.NeverDoThese , 'A') = ISNULL ( CrsApp.NeverDoThese , 'A')
AND ISNULL ( BG.Privileged , 'A') = ISNULL ( CrsApp.Privileged , 'A')
AND ISNULL ( BG.InquiryOnly , 'A') = ISNULL ( CrsApp.InquiryOnly , 'A')
AND ISNULL ( BG.Department , 'A') = ISNULL ( CrsApp.Department , 'A')
AND 1 = ( -- This is how i am finding the single users with
-- base security combination
SELECT COUNT(*)
FROM Permissions InnerCntQuer
WHERE InnerCntQuer.UserID = CrsApp.UserID
GROUP BY InnerCntQuer.UserID , InnerCntQuer.UserID
)
)
)
SELECT * FROM BaseGrps
UNION ALL
SELECT * FROM UsersSecurityOtherThanBaseGrps
UNION ALL
SELECT * FROM SingleUsersWithBaseGrps
Hope that helps 🙂
May 20, 2011 at 11:55 am
ColdCoffee, that helps very much. Thanks for taking the time to write that!
Mike Scalise, PMP
https://www.michaelscalise.com
May 20, 2011 at 12:25 pm
mikes84 (5/20/2011)
ColdCoffee, that helps very much. Thanks for taking the time to write that!
You're welcome, Mike.. does that query fit your requirement? And were the comments enough and informative?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply