Use Boolean algebra in tsql to avoid CASE statement or deal complex WHERE conditions

Question

I came across a scenario,I will explain it with some dummy data. See the table Below

Select * from LUEmployee

empId   name    joiningDate
1049    Jithin  3/9/2009
1017    Surya   1/2/2008
1089    Bineesh 8/24/2009
1090    Bless   7/15/2009
1014    Dennis  1/5/2008
1086    Sus     9/10/2009

I need to increment the year column by 1, only If the months are Jan, Mar, July Or Dec.

empId   name    joiningDate derived Year
1049    Jithin  3/9/2009    2010
1017    Surya   1/2/2008    2009
1089    Bineesh 8/24/2009   2009
1090    Bless   7/15/2009   2010
1014    Dennis  1/5/2008    2009
1086    Sus     9/10/2009   2009

derived Year is the required column

We were able to achieve this easily with a case statement like below

Select *,
YEAR(joiningDate) + CASE WHEN MONTH(joiningDate) in (1,3,7,12) THEN 1 ELSE 0 END 
from LUEmployee

But there came an added condition from onsite PM, Dont use CASE statement, CASE is inefficient. Insearch of a soultion, We resulted in a following solution, a solution using binary K-map, As follows

---

If number 1 to 12 represents months from Jan to Dec, See the binary result

the Karnaugh Map way of expressing is given below.

the result will be

We need to realize the expression with sql server binary operations

eg: binary of 12 = 1100
    in the k-map, a = 1, b = 1, c = 0, d = 0
    Similarly, binary of 7 = 0111
    in the k-map, a = 0, b = 1, c = 1, d = 1

to get the left most bit (d), we will have to shift the bit towards right by 3 positions and the mask all the bits except LSB.

eg: ((MONTH(joiningDate)/8)&1)

Similarly, second bit from left (c), we need to shift the bit towards right by 2 positions and then mask all the bits except LSB

eg: ((MONTH(joiningDate)/4)&1)

Finally, each bit can be represented as

so  a = ((MONTH(joiningDate)/8)&1)
    b = ((MONTH(joiningDate)/4)&1)
    c = ((MONTH(joiningDate)/2)&1)
    d = (MONTH(joiningDate)&1)

a inverse = (((MONTH(joiningDate)/8)&1)^1)
b inverse = (((MONTH(joiningDate)/4)&1)^1)
c inverse = (((MONTH(joiningDate)/2)&1)^1)
d inverse = ((MONTH(joiningDate)&1)^1)

The final code will be

SELECT  *,
        YEAR(joiningDate) + CAST(
        ((MONTH(joiningDate)/8)&1)*((MONTH(joiningDate)/4)&1)*(((MONTH(joiningDate)/2)&1)^1)*((MONTH(joiningDate)&1)^1) |
        (((MONTH(joiningDate)/8)&1)^1)*(((MONTH(joiningDate)/4)&1)^1)*(MONTH(joiningDate)&1) |
        (((MONTH(joiningDate)/8)&1)^1)*((MONTH(joiningDate)/2)&1)*(MONTH(joiningDate)&1) 
        AS INT) [derivedYear]
FROM    LUEmployee

Result will be

---

Question: There may be simple and less complex ideas, please share it.

I like to find a simpler one ,as well as share the idea.Here the possible conditions are 12 (12 months). We can use k-map for even bigger number of conditions..Felt like k-map is convenient for up to 64 conditions.

Answer 1

My first reaction would be to defend the use of the case clause in this case. But if you are absolutely not allowed to use it, maybe you could simply add a table with the month and increment values:

LUMonthIncrement

Month   Increment
 1      1  
 2      0  
 3      1  
 4      0  
 5      0  
 6      0  
 7      1  
 8      0  
 9      0  
10      0  
11      0  
12      1  

Then you can join in that table and just add the increment:

Select LUEmployee.*,
    YEAR(joiningDate) + LUMonthIncrement.Increment as derivedYear
from LUEmployee
    join LUMonthIncrement on MONTH(LUEmployee.joiningDate) = LUMonthIncrement.Month

This is unlikely to be much more performant though, because in order to join to LUMonthIncrement the MONTH(LUEmployee.joiningDate) expression must be evaluated for each row in the LUEmployee table.