set nocount on
if object_id('tempdb..#PeriodTypes', 'u') is not null
    drop table #PeriodTypes
go

if object_id('tempdb..#Reports', 'u') is not null
    drop table #Reports
go

create table #PeriodTypes
    (ID int identity(1,1),
     Period varchar(50),
     Value int,
     Active bit)

insert into #PeriodTypes values ('None', 0, 0)
insert into #PeriodTypes values ('Monthly - Inception To Date', 1, 1)
insert into #PeriodTypes values ('Annual', 2, 0)
insert into #PeriodTypes values ('Semi Annual', 4, 0)
insert into #PeriodTypes values ('Quarterly', 8, 0)
insert into #PeriodTypes values ('Monthly', 16, 1)
insert into #PeriodTypes values ('Weekly', 32, 0)
insert into #PeriodTypes values ('Daily', 64, 0)
insert into #PeriodTypes values ('Rolling 12 Months', 128, 1)
insert into #PeriodTypes values ('Rolling 6 Months', 256, 1)
insert into #PeriodTypes values ('Rolling 3 Months', 512, 1)
insert into #PeriodTypes values ('Rolling 24 Months', 1024, 1)
insert into #PeriodTypes values ('Rolling 36 Months', 2048, 1)
insert into #PeriodTypes values ('Annual x2', 4096, 0)
insert into #PeriodTypes values ('Annual x3', 8192, 0)
insert into #PeriodTypes values ('Quarterly - Inception To Date', 16384, 1)
insert into #PeriodTypes values ('Annual - Inception To Date', 32768, 1)
insert into #PeriodTypes values ('Calendar - Year To Date', 65536, 1)


create table #Reports
    (ReportTypeID int identity(1,1),
     Title varchar(50),
     PeriodType int)

insert into #Reports values ('Overall Performance (Original)', 256)
insert into #Reports values ('Overall Performance', 16272)
insert into #Reports values ('Referral Drivers', 16272)
insert into #Reports values ('Sales From Referrals', 16272)
insert into #Reports values ('Z_KRD', 16272)
insert into #Reports values ('Blank', 16272)
insert into #Reports values ('Trend: Category Summary', 49153)
insert into #Reports values ('Category Summary', 16272)
insert into #Reports values ('Personnel Ratings', 16272)
insert into #Reports values ('Z_Internal Test', 16272)
insert into #Reports values ('Question Detail', 16272)
insert into #Reports values ('Trend: Question Detail', 49153)
insert into #Reports values ('Trend: Response Rate', 49153)
insert into #Reports values ('Key Measure Trend', 16272)
insert into #Reports values ('Ranking', 16272)
insert into #Reports values ('Comments', 16272)
insert into #Reports values ('Key Measures National Average', 16272)
insert into #Reports values ('Virtual Survey', 112)

select distinct t.ID, t.Period, t.Value, r.PeriodType
from #Reports r
join #PeriodTypes t on t.Active = 1
where t.Value & r.PeriodType = t.Value

select distinct t.ID, t.Period, t.Value, r.PeriodType, t.Value & r.PeriodType as 'Evaluation'
from #Reports r
join #PeriodTypes t on t.Active = 1

ID	Period				Value	PeriodType	Evaluation
2	Monthly - Inception To Date	1	112		0
2	Monthly - Inception To Date	1	256		0
2	Monthly - Inception To Date	1	16272		0
2	Monthly - Inception To Date	1	49153		1
6	Monthly				16	112		16
6	Monthly				16	256		0
6	Monthly				16	16272		16
6	Monthly				16	49153		0
9	Rolling 12 Months		128	112		0
9	Rolling 12 Months		128	256		0
9	Rolling 12 Months		128	16272		128

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

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

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

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

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

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

DECLARE @PeriodType VARCHAR(50)
SET @PeriodType = 'Monthly'

-- Pull all Monthly Reports
SELECT
	r.title
FROM
	#reports AS r
	JOIN
		#PeriodTypes AS pt
	ON	r.PeriodType & pt.value = pt.Value
WHERE
	pt.Period = @PeriodType