sql query to sum the mask values!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query to sum the mask values!!

Author

Topic

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-29 : 22:43:05

Hi All,

I need a sql query to sum the mask values :

Ex: 1. sum of 4 and 15 is 15 as 4 is already in 15
2, Sum of 1 and 3 is 3
3. Sum of 1 and 2 is 3.

kindly let me know the sql query for this.

Thanks,
Rams

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 23:07:09

What would be the SUM of 20 and 5. As per your logic again the result should be 20 as 5 is already in 20. Doesn't that mean you always want the MAX of the two numbers?

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 02:16:37

HI Vadivel,

I want to know how do we use bitmasks first and how to do sum of bitmasking.could you provide me some good examples on the same.
Thanks,
Ram

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-30 : 03:34:20

May be you are looking for this then:

DECLARE @tblTestBit TABLE
(
iValue INT
)

INSERT @tblTestBit
SELECT 4 UNION
SELECT 15

DECLARE @counter INT
SET @counter = 0

SELECT @counter = @counter | iValue
FROM @tblTestBit

--Result
SELECT @counter as [Result]

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 05:04:08

Hi Vadivel,

Iam having the following table and values :

create table BitmaskDemo
(ID int Primary key
,Supermarket varchar(10)
,bitmask varbinary(1000)
)

/*
values
1 = Banana 00000001
2 = Strawberries 00000010
4 = Oranges 00000100
8 = Apples 00001000
16 = plums 00010000
32 = pineapple 00100000
*/

Insert into Bitmaskdemo
values (1,'Tesco',7) -- Bananas,strawberries & oranges 00000111
Insert into Bitmaskdemo
values (2,'Sainsburys',31) -- Bananas, strawberries, oranges, apples & plums 00011111
Insert into Bitmaskdemo
values (3,'Morrissons',8) -- Apples 00001000
Insert into Bitmaskdemo
values (4,'Waitrose',24) -- Apples and plums 00011000
Insert into BitmaskDemo
values (5,'Aldi',25) -- Bananas, Apples and Plums 00011001
Insert into BitmaskDemo
values (6,'Somerfield',9) -- Bananas & Apples 00001001

And i want the sql query to get sum of bitmasks as follows :
sum of 4 and 15 is 15 ,
Sum of 1 and 3 is 3 ,
and Sum of 1 and 2 is 3

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-30 : 05:20:22

I dont understand from where does 15 came because it doesn't seem to be there in your INSERT script.

I think the logic which i have in my previous post would give you the result which you are asking for. I have used INT as the column datatype.

In your example you seems to have been using VARBINARY as your datatype. Then you got to convert it into INT and still use the same sample.

DECLARE @tblTestBit TABLE
(
iValue varbinary(1000)
)

INSERT @tblTestBit
SELECT 1 UNION SELECT 3

DECLARE @counter INT
SET @counter = 0

SELECT @counter = @counter | CONVERT(INT,iValue)
FROM @tblTestBit

--Result
SELECT @counter as [Result]

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 05:30:40

Hi Vadivel,

Thanks for your immediate response. Could you please implement your code for my example and show me the output please. That would be a great help for me.

Thanks,
Ram

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-30 : 05:35:41

You are inserting some 6 records into the table. Can you pls explain based on what condition you want to add those records 1 & 2 OR 1 & 3?

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 05:39:59

Hi Vadivel,

I have used numbers in the power of 2 in this example. This ensures that every value gets assigned a unique number and that the sum of these numbers will always generate a unique combination. So bananas and oranges = 5.

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-30 : 05:40:15

Here you go, I am just adding the rows whose ID is 1 or 3.

DECLARE @counter INT
SET @counter = 0

/*
SELECT @counter = @counter | CONVERT(INT, bitmask)
FROM Bitmaskdemo WHERE ID in (1,3)
*/

SELECT @counter = @counter | CONVERT(INT, bitmask)
FROM Bitmaskdemo WHERE ID = 1 or ID = 3

--Results
SELECT @Counter as [Results]

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 05:48:23

Hi Vadivel,

I will be selecting the table based on bitmask values :

eg: select * from Bitmaskdemo where Bitmask & 17 = 17

in the same way i need for SUM of bitmasks if 1 & 2 as 3..etc

Thanks,
Ram

rams

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 06:02:04

it is fine if you provide me the code without using bit operators also

rams

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-30 : 06:10:09

Not sure what exactly you are trying to achieve! May be you need to write the question more clearly on what exactly you want. The below query is my last try. If this isn't want you want you have to write your question as clearly as possible with sample data and tell exactly what you want to achieve from your sample data.

SELECT * FROM Bitmaskdemo WHERE CONVERT(INT, bitmask) | 8 = 15

Best Regards
Vadivel

http://vadivel.blogspot.com

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 06:28:35

HI Vadivel,

Sorry if had troubled you much. Here is the detailed question :
As shown From the table Bitmaskdemo we are having bitmaskcolumn as well.
Now the clear requirement of mine is
I need to SUM the mask values and make the output as :
1. SUM of 1 & 3 gives 3
2. SUM of 1 & 2 gives 3.

The example which u have given is giving the exact ouput as shown below:

DECLARE @tblTestBit TABLE
(
iValue varbinary(1000)
)

INSERT @tblTestBit
SELECT 1 UNION SELECT 3

DECLARE @counter INT
SET @counter = 0

SELECT @counter = @counter | CONVERT(INT,iValue)
FROM @tblTestBit

--Result
SELECT @counter as [Result]
I wanted to know how this to be implemented in the real code..sql query:)

rams

jimoomba
Yak Posting Veteran

90 Posts

Posted - 2011-09-30 : 06:33:58

Hi Vadivel,

It would be clear for me if you coulld create a samole table with multiple columns and give one good example :)
I feel really motivated by the way u are guiding me. Very thankful to u for ur efforts. Iam new to this field..Please dont mind if i irriitate u with repeated questions or so...

Thanks a lot :)

rams

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources