Ahhh, bitvector (bitmask, bitwise operators, combined flags, flagfield, etc...), my old friend.

Alright, let's start with the basics. You said you've read up on it but some of your difficulty shows that some of the basics didn't quite make it through.

Let's look over a byte for a second:

128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

Why backwards? Because that's actually how it is to the system. If we have the value of 37 in there, we get this:

128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

0 0 1 0 0 1 0 1

I assume you're with me so far. Now, the trick is understanding what a bitwise comparison for AND, OR and XOR mean. We'll avoid XOR, you can get it after a few reads after understanding the rest.

So, we're going to test 37 with 8. The code is what you've typed: 37 & 8 = 8. It basically checks that the 8 bit is turned on. What's it DOING though?

128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

0 0 1 0 0 1 0 1 - 37

0 0 0 0 1 0 0 0 - 8

------------------------------------

0 0 0 0 0 0 0 0

They don't match. The particular bit(s) we're checking for (in this case, 8) isn't turned on in BOTH values for the check, so everything zeroes out.

Now, if we OR'd them ( 37 | 8 = 8)... which is somewhat useless in this scenario but to be clear, you'd get this:

128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

0 0 1 0 0 1 0 1 - 37

0 0 0 0 1 0 0 0 - 8

------------------------------------

0 0 1 0 1 1 0 1 - 45

OR's are usually against what you need to do but they can be helpful when combining extended flags. In this case, by the way, your OR statement would end up false to the conditional check.

Now, let's say instead of flag 8, we want flag 4: 37 & 4 = 4

128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

0 0 1 0 0 1 0 1 - 37

0 0 0 0 0 1 0 0 - 4

------------------------------------

0 0 0 0 0 1 0 0 - 4

Hey, a hit! That's a true condition.

With those basics, let's take a closer look at some code:

SELECT

r.Title,

CASE WHEN r.PeriodType = 0 THEN 'SET' ELSE NULL END AS [NoSetting],

CASE WHEN r.PeriodType & POWER( 2, 0) = POWER( 2, 0) THEN 'SET' ELSE NULL END AS [Monthly - Inception To Date],

CASE WHEN r.PeriodType & POWER( 2, 1) = POWER( 2, 1) THEN 'SET' ELSE NULL END AS [Annual],

CASE WHEN r.PeriodType & POWER( 2, 2) = POWER( 2, 2) THEN 'SET' ELSE NULL END AS [Semi Annual],

CASE WHEN r.PeriodType & POWER( 2, 3) = POWER( 2, 3) THEN 'SET' ELSE NULL END AS [Quarterly],

CASE WHEN r.PeriodType & POWER( 2, 4) = POWER( 2, 4) THEN 'SET' ELSE NULL END AS [Monthly],

CASE WHEN r.PeriodType & POWER( 2, 5) = POWER( 2, 5) THEN 'SET' ELSE NULL END AS [Weekly],

CASE WHEN r.PeriodType & POWER( 2, 6) = POWER( 2, 6) THEN 'SET' ELSE NULL END AS [Daily],

CASE WHEN r.PeriodType & POWER( 2, 7) = POWER( 2, 7) THEN 'SET' ELSE NULL END AS [Rolling 12 Months],

CASE WHEN r.PeriodType & POWER( 2, 8) = POWER( 2, 8) THEN 'SET' ELSE NULL END AS [Rolling 6 Months],

CASE WHEN r.PeriodType & POWER( 2, 9) = POWER( 2, 9) THEN 'SET' ELSE NULL END AS [Rolling 3 Months],

CASE WHEN r.PeriodType & POWER( 2,10) = POWER( 2,10) THEN 'SET' ELSE NULL END AS [Rolling 24 Months],

CASE WHEN r.PeriodType & POWER( 2,11) = POWER( 2,11) THEN 'SET' ELSE NULL END AS [Rolling 36 Months],

CASE WHEN r.PeriodType & POWER( 2,12) = POWER( 2,12) THEN 'SET' ELSE NULL END AS [Annual x2],

CASE WHEN r.PeriodType & POWER( 2,13) = POWER( 2,13) THEN 'SET' ELSE NULL END AS [Annual x3],

CASE WHEN r.PeriodType & POWER( 2,14) = POWER( 2,14) THEN 'SET' ELSE NULL END AS [Quarterly - Inception To Date],

CASE WHEN r.PeriodType & POWER( 2,15) = POWER( 2,15) THEN 'SET' ELSE NULL END AS [Annual - Inception To Date],

CASE WHEN r.PeriodType & POWER( 2,16) = POWER( 2,16) THEN 'SET' ELSE NULL END AS [Calendar - Year To Date]

FROM

#reports AS r

As you can see, you need to consider each flag (or flag combination) like they were their own column. Bitwise operations are the fastest compare you can do on large volumes of data, where multiplying them into many to many tables just overloads the collection. Unless you're looking at millions of reports, I wouldn't even go near this method for a collection this small.

As a reference, I'll use Bitvectors in my fact tables of billion+ rows for common search patterns so that I can preprocess the search pattern and have the bitvector inform of the success without needing to go to the dimensions. It's very tight for storage and I find them very useful. They're also a specialty tool that should be avoided for common coding.

EDIT:

Heh, sorry, left off a piece. In a case like this, you'd use it to pull all monthly reports, doing something like the following:

DECLARE @PeriodType VARCHAR(50)

SET @PeriodType = 'Monthly'

-- Pull all Monthly Reports

SELECT

r.title

FROM

#reports AS r

JOIN

#PeriodTypes AS pt

ONr.PeriodType & pt.value = pt.Value

WHERE

pt.Period = @PeriodType