BIT-Wise Aggregation

Hi,

I have the following three tables :
Account (Id int, AccountName nvarchar(25))
Role (id int, Rights int)
AccountRole (AccountID, RoleID)

In Role table - Rights Column is a bit map where in each bit would refer to access to a method.
One account can be associated with multiple roles - AccountRole table is used for representing the N:N relation.

I want to develop a store procedure - which would return all AccountName and their Consolidated Rights.
Basically I want to do a BitWise OR operation for all the Rights in the Aggregation instead of the SUM as shown in the following statement.

SELECT Account.Name, SUM(Role.Rights) FROM Account WITH (NOLOCK)
JOIN RoleAccount ON RoleAccount.AccountID = Account.Id
JOIN Role ON RoleAccount.RoleId = Role.Id
GROUP BY Account.Name

Thanks,
Loonysan

Here is code that shows a trick. The idea is to break each "rights mask" into a series of rows representing the separate bit values. These can be recombined with a Sum(distinct) across the roles owned by each account.

To make this work, you need a table that has one row for each bit position. My example shows a subset of bits built using a union. There is a system table called spt_values that holds a bunch of useful values used by system stored procedures. It contains rows for each bit position and would work with this application.

Drop Table #AccountRoles

Drop Table #Account

Drop Table #Role

go

Create Table #Account(

account_id int Not Null Identity( 1000, 100 ),

account_name varchar(100) Not Null

)

Create Table #Role(

role_id int Not Null Identity( 100, 1 ),

role_name varchar(100) Not Null,

rights int Not Null

)

Create Table #AccountRoles(

account_id int Not Null,

role_id int Not Null

)

go

Insert #Account values( 'Fred' )

Insert #Account values( 'Barney' )

Insert #Account values( 'Wilma' )

Insert #Account values( 'Betty' )

go

Select * from #Account

go

Insert #Role values ( 'Only 1', 1 )

Insert #Role values ( 'OneThree', 5 )

Insert #Role values ( 'JustTwo', 2 )

Insert #Role values ( 'All', 7 )

go

Select * from #Role

Insert #AccountRoles values ( 1100, 100 )

Insert #AccountRoles values ( 1100, 102 )

Insert #AccountRoles values ( 1200, 100 )

Insert #AccountRoles values ( 1200, 101 )

Insert #AccountRoles values ( 1300, 101 )

Insert #AccountRoles values ( 1300, 103 )

go

Select acc.account_id,

acc.account_name,

Sum(Distinct right_explode.single_right )

From #Account acc

Left Join #AccountRoles acr

On acr.account_id = acc.account_id

Join (

Select role_id,

bitposition,

rights & bitposition single_right

From #Roles

Cross Join

(

Select 1 as bitposition

Union

Select 2 as bitposition

Union

Select 4 as bitposition

Union

Select 8 as bitposition

Union

Select 16 as bitposition

) as bits

) right_explode

On right_explode.role_id = acr.role_id

Group

By acc.Account_Id,

acc.Account_Name

Order

By acc.Account_Name

Another approach would be to make a function that computed the bit-wise Or for a single account. This could be done using a local variable and a Select statement. This is not as good a solution.

Declare @.bitsum int

Select @.bitsum = bitsum | rights

From #AccountRoles ar

Join #Role r

On r.role_id = ar.role_id

Where ar.account_id = @.account

return @.bitsum|||

It works:

create table Account (Id int, AccountName nvarchar(25));

create table Role (id int, Rights int);

create table AccountRole (AccountID int, RoleID int);

insert into account values (1, 'DEMO');

insert into account values (2, 'TEST');

insert into role values (1, 125);

insert into role values (2, 225);

insert into AccountRole values (1, 1);

insert into AccountRole values (2, 1);

insert into AccountRole values (2, 2);

alter function f_or(@.acc_id int) returns int

begin

declare @.right int,

@.result int;

set @.result = 0

declare lcursor cursor for select rights from Role R, AccountRole AR where R.id = AR.roleid and AccountId = @.acc_id;

open lcursor;

fetch lcursor into @.right;

while @.@.FETCH_STATUS = 0 begin

set @.result = @.result | @.right

fetch lcursor into @.right

end

close lcursor;

return @.result;

end

go

select AccountName, dbo.f_or(id) 'Rights' from account

|||

Why do you think the second approach is not a good solution?

Thanks,
Loonysan

|||

If you only need to get the rights for a single Account, then the approach would be fine. However, in a set-wise report, my guess is that it would perform much worse as you are producing a new query for each Account instead of joining the tables in.

I only sketched what the function would look like. Can you complete the function or do you need more code? If you didn't simplify your tables or the problem, in a major way, for the sake of the post it should be easy enough to try both methods and weigh the advantages.