Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Even Odd Count Puzzle

This question was asked to me in one of the technical interviews I had recently attended. Here we have to find out count how many even values and odd values are there per row.

Please check out the sample input values and sample expected output below.

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE EvenOddCount
(
	  a SMALLINT
	 ,b SMALLINT
	 ,c SMALLINT
	 ,d SMALLINT
)
GO

INSERT INTO EvenOddCount VALUES
(1,5,7,10),
(2,5,17,0),
(9,51,7,1),
(7,13,7,3),
(12,5,8,4),
(16,5,6,0)

GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using %


--

SELECT a,b,c,d, ( (a+1) % 2 + (b+1) % 2 + (c+1) % 2 + (d+1) % 2 ) EvenCount
			  , ( (a) % 2 + (b) % 2 + (c) % 2 + (d) % 2 ) OddCount
FROM EvenOddCount

--

SOLUTION 2 | Using Simple Maths


--

SELECT a,b,c,d, 4 - ( (a) % 2 + (b) % 2 + (c) % 2 + (d) % 2 ) EvenCount
			  , ( (a) % 2 + (b) % 2 + (c) % 2 + (d) % 2 ) OddCount
FROM EvenOddCount	 

--

SOLUTION 3 | Using Simple Maths 1


--

SELECT a,b,c,d , 4-u EvenCount , u OddCount
FROM EvenOddCount
CROSS APPLY 
(
	VALUES(a%2 + b%2 + c%2 + d%2) 
)t(u)

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements