October 22, 2014 at 2:30 am
I am trying to get a culmulative Bitwise OR operation on a column by query - rather than using a CLR function.
The problem is shown below:
CREATE TABLE #Operations (
PK INT NOT NULL IDENTITY(1,1),
UserName VARCHAR(50) NOT NULL,
UserProcess VARCHAR(50) NOT NULL,
ServerOperation VARCHAR(50) NOT NULL,
Permission INT NOT NULL );
INSERT INTO #Operations (UserName, UserProcess, ServerOperation, Permission)
SELECT 'Tom', 'Ledger', 'Finance', 1 UNION ALL
SELECT 'Tom', 'Ledger', 'Finance', 2 UNION ALL
SELECT 'Tom', 'Ledger', 'Finance', 4 UNION ALL
SELECT 'Bill', 'Dispatch','Export', 2 UNION ALL
SELECT 'Bill', 'Dispatch','Export', 4 UNION ALL
SELECT 'Mary', 'Audit', 'Finance', 1 UNION ALL
SELECT 'Mary', 'Invoice','Export', 1 UNION ALL
SELECT 'Mary', 'Invoice','Finance', 8 UNION ALL
SELECT 'Tom', 'Audit', 'Finance', 15 UNION ALL
SELECT 'Mary', 'Dispatch','Export', 3
SELECT * FROM #Operations;
-- Get the full permissions a user has on a Server Operation
-- Solution: Bitwise OR on all permissions by user/serverOperation
--e.g. Tom/Finance perms = 1 | 2 | 4 | 15 = 15
-- Mary/Export perms = 1 | 3 = 3
DECLARE @BITFLAG INT = 1 | 2 | 4 | 15;
SELECT @BITFLAG AS BITFLAG
-- Expected Output
UserName, ServerOperation, Permission
Bill, Export, 6
Mary, Export, 3
Mary, Finance, 9
Tom, Finance, 15
So Far I've tried SUM - wrong results, and STUFF - which doesn't seem appropriate for bitwise operation - neither give useful results.
-- SUM Operation - fails
SELECT DISTINCT
UserName,
ServerOperation,
SUM(Permission) AS Permission
FROM #Operations
GROUP BY UserName, ServerOperation
-- STUFF 'OR' bitwise operation XML PATH
SELECT DISTINCT
Op2.UserName,
Op2.ServerOperation,
STUFF( (SELECT 0 | Op1.Permission
FROM #Operations AS Op1
WHERE Op1.PK = Op2.PK
FOR XML PATH('')),1,1,'') AS Permission
FROM #Operations AS Op2
GROUP BY Op2.UserName, Op2.ServerOperation, Op2.PK
It may be possible to materialise the permissions each time one is changed (e.g. by to use a cursor ), however I would prefer a query or function or combination to generate the result
October 22, 2014 at 3:00 am
Interesting.
If you could find a way of breaking down the permission at row level (eg, from 7 to (4,2,1)), it may be possible to do a SUM() on the UNIONed permissions (with the UNION taking only distinct values). Not straightforward though. I'm sure that others will have better ideas.
--Edit: fixed incorrect example of breakdown of powers of 2
October 22, 2014 at 3:36 am
You can do it by breaking each permission down into a sum of powers of two. Something like this should work:
SELECT UserName, ServerOperation, SUM(DISTINCT p)
FROM #Operations o
OUTER APPLY (
SELECT p FROM (
SELECT POWER(2,n) FROM (
VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8)
) Nos(n)
) Powers(p)
WHERE p & o.Permission = p
) Bitmask
GROUP BY UserName, ServerOperation
John
October 22, 2014 at 5:13 am
John Mitchell-245523 (10/22/2014)
You can do it by breaking each permission down into a sum of powers of two. Something like this should work:
SELECT UserName, ServerOperation, SUM(DISTINCT p)
FROM #Operations o
OUTER APPLY (
SELECT p FROM (
SELECT POWER(2,n) FROM (
VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8)
) Nos(n)
) Powers(p)
WHERE p & o.Permission = p
) Bitmask
GROUP BY UserName, ServerOperation
John
Awesome work John
October 22, 2014 at 5:18 am
Thanks John,
Brilliant lateral thinking.
October 22, 2014 at 5:20 am
Blush! Thanks, guys.
John
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy