REATE TABLE #Puzzle(Col1 int, Col2 int, Col3 int, Col4 int, Col5 int) INSERT #Puzzle VALUES (1,2,3,4,5) INSERT #Puzzle VALUES (0,1,2,3,4) INSERT #Puzzle VALUES (2,2,2,2,2) INSERT #Puzzle VALUES (1,1,1,1,1) INSERT #Puzzle VALUES (3,2,3,2,3) INSERT #Puzzle VALUES (2,3,2,3,2) |

And here is the expected output

Col1 Col2 Col3 Col4 Col5 Even 1 2 3 4 5 2 0 1 2 3 4 3 2 2 2 2 2 5 1 1 1 1 1 0 3 2 3 2 3 2 2 3 2 3 2 3

That should be pretty easy right?

How about a solution that doesn’t use a CASE statement, you think you can do it (I can think of 2 ways without using a CASE statement)

Select Col1, Col2, Col3, Col4, Col5,

5 – (Col1 % 2 + Col2 % 2 + Col3 % 2 + Col4 % 2 + Col5 % 2) As Even

5 – (Col1 % 2 + Col2 % 2 + Col3 % 2 + Col4 % 2 + Col5 % 2) As Even

SELECT #Puzzle.*, 5 – SUM(remainder) AS EVEN

from #Puzzle

CROSS APPLY (VALUES

(Col1 % 2),

(Col2 % 2),

(Col3 % 2),

(Col4 % 2),

(Col5 % 2) ) Even (remainder)

GROUP BY Col1, Col2, Col3, Col4, Col5

from #Puzzle

CROSS APPLY (VALUES

(Col1 % 2),

(Col2 % 2),

(Col3 % 2),

(Col4 % 2),

(Col5 % 2) ) Even (remainder)

GROUP BY Col1, Col2, Col3, Col4, Col5

SELECT Col1,Col2,Col3,Col4,Col5,

(~Col1%2 + ~Col2%2 + ~Col3%2 + ~Col4%2 + ~Col5%2 )*-1 as EVENCOUNT

FROM #Puzzle

(~Col1%2 + ~Col2%2 + ~Col3%2 + ~Col4%2 + ~Col5%2 )*-1 as EVENCOUNT

FROM #Puzzle

The Solution is: Use (Modulo) %2 which gives remainder after dividing Value by 2 : So for odd numbers it will be 1 and even numbers it will be 0 .. we want to count the even numbers so use Tilde ~ to get a Binary NOT of the value and apply Mod.. This will give -1 for all even numbers and 0 for all odd numbers.. sum all this and multiply result by -1 to get final count of even numbers !!

There is a lot of debate whether zero is odd or even .. but many sources agree zero is even number. This solution will also count zero as an even number.

## No comments:

## Post a Comment