Dfyjkt

Probably overthinking this. Issue is I've inherited a database where a member can have two (or more) addresses as part of membership (think seasonal) record (addresses are in separate records), and database actions depend on which address the member is currently located at.

Currently, each month (Jan, Feb, etc.) is a bit field. So in order to query where a member is this month (or span of months) you have to either select for the field name that matches current month (or month span for period), meaning you have to query by variable field name... or scan through the 12 fields - clumsy. Seems to me it should either be an integer field that is queried by bitwise xor operation (one for which months are flagged and one for query period), or by "adding" the field values into a string of bits converted to integer number then using bitwise comparison.

Second option would be ok (wouldn't change the table structure) but would rather use one integer field to represent a binary version of the 12 months then work with that.

In either case, been way too many years since I did much bitwise work, much less figure out how to convert into binary representation. I expect there is already a published answer somewhere, but can't find it. Any suggestions? Thanks!

Since you are open to using an integer field, add a column to your table to hold the month numeric values.

alter table myTable
add column MonthNum int

alter table myTable
add constraint CK_Month_Range CHECK (MonthNum > 0 and MonthNum < 13)

There wouldn't be any benefit of storing a bit representation of the month number in an integer field since the same amount of space will be taken up (4 bytes). Many system table use bitwise values but in this case, I don't see how it would improve anything.

In order to check if they are there, I would probably do one of two things.

• Use a bit value column for IsCurrent


• Use a form of row versioning where the last updated datetime would be the current record

Here is a bitwise type of check if your application will send in an integer for the parameter

declare @table table (id int identity(1,1), jan bit, feb bit, mar bit, apr bit, may bit, jun bit, jul bit, aug bit, sep bit, oct bit, nov bit, [dec] bit)

insert into @table
values
(0,0,0,0,0,0,0,1,0,0,0,0), --aug
(0,0,1,0,0,0,0,0,0,0,0,0), --mar
(0,0,0,0,0,0,0,0,0,0,0,1), --dec
(1,0,0,0,0,0,0,0,0,0,0,0) --jan


declare @month int = 3

select *, bitwise = concat(jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,[dec])
from @table
where charindex('1',concat(jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,[dec])) = @month

If they are passing in strings, like 'January', then you'd just join to a lookup table.