Manipulating different combinations of Week Days with single argument using bitwise operation

This article examines how to use bitwise operation in order to manipulate data related to different combinations of week days using single argument in T SQL.

Imagine situation where we would like to execute CRUD operations on data related to particular one or combination of Week Days.

These kinds of tasks may be found in assets pricing and availability management applications.

Let say we have a property that we would like to rent and we also would like to price it differently depending on week days.

How many arguments for filtering our data do we need if we let’s say desire to set the same price for any combination of two, three, four or more week days with one operation?

As you can guess, declaring many arguments (to cover all week days and their different combinations) to be included in “WHERE” clause is not the best solution.

Fortunately we can manage it with one argument and use bitwise operation to identify all week day’s combinations of our interest.

For illustration purpose let’s create imaginary [CalendarDays] table with following schema.

The [WeekDay] column is a computed one where each value representing Week day as integer starting with “Sunday” equal to 1. (Added for visibility only)

Let’s populate our table with dates of some month and fill [Data] column with string representation of Week days for clarity.

Now, how can we select records with data for let's say Thursday, Friday and Monday with one argument?

The trick is to add computed column (either to table or to query) which calculates Exponentiation with base of 2 and power of integer, representing Week day.

2ⁿ where n = DATEPART(weekday, [Date])

or

2ⁿ where n = DATEPART(weekday, [Date]) - 1

in order for n to start from "0"

Now in order to select our combination of Thursday, Friday and Monday we can sum up the values for those days from [WeekDayExpo] column and use this value as argument in “WHERE” clause with “&” (Bitwise AND) operator in target query.

The sum is 50

[16 (for Thursday) + 32 (for Friday) + 2 (for Monday) ]

As we can see all rows for combination of Week days we wanted to manipulate are selected and we used single argument in our query.

Happy programming,

Alex Movshovich,
Software Developer.