Recently I found this interesting question asked in a SQL forum,

Let’s say you have 5 columns in a table and you want to know whether these columns values satisfies the following conditions,

- Three columns should have
**values > 19**
- Two columns should have
**values < 20**
- Two columns should have values in
**Odd**
- Three columns should have values in
**Even**

I have tried and come up with the following query. Share your thoughts and queries through comments,

[sourcecode language=”sql” wraplines=”false”]

declare @table table (Col1 int,Col2 int,Col3 int,Col4 int,Col5 int)

insert into @table

select 4, 7, 25, 38, 40 union

select 2, 5, 21, 40, 42 union

select 2, 4, 18, 17, 15 union

select 1, 3, 19, 17, 15 union

select 2, 4, 21, 22, 23 union

select 1, 5, 45, 40, 48 union

select 1, 42, 40, 5, 56

;with cte as (

select Col1,Col2,Col3,Col4,Col5,

case when Col1%2=0 then 1 else 0 end EvenCheck1,

case when Col2%2=0 then 1 else 0 end EvenCheck2,

case when Col3%2=0 then 1 else 0 end EvenCheck3,

case when Col4%2=0 then 1 else 0 end EvenCheck4,

case when Col5%2=0 then 1 else 0 end EvenCheck5,

case when Col1%2=1 then 1 else 0 end OddCheck1,

case when Col2%2=1 then 1 else 0 end OddCheck2,

case when Col3%2=1 then 1 else 0 end OddCheck3,

case when Col4%2=1 then 1 else 0 end OddCheck4,

case when Col5%2=1 then 1 else 0 end OddCheck5,

case when Col1>19 then 1 else 0 end GtrCheck1,

case when Col2>19 then 1 else 0 end GtrCheck2,

case when Col3>19 then 1 else 0 end GtrCheck3,

case when Col4>19 then 1 else 0 end GtrCheck4,

case when Col5>19 then 1 else 0 end GtrCheck5,

case when Col1<20 then 1 else 0 end LsrCheck1,

case when Col2<20 then 1 else 0 end LsrCheck2,

case when Col3<20 then 1 else 0 end LsrCheck3,

case when Col4<20 then 1 else 0 end LsrCheck4,

case when Col5<20 then 1 else 0 end LsrCheck5

from @table

)

select Col1,Col2,Col3,Col4,Col5 from cte

where EvenCheck1+EvenCheck2+EvenCheck3+EvenCheck4+EvenCheck5 = 3

and OddCheck1+OddCheck2+OddCheck3+OddCheck4+OddCheck5 = 2

and GtrCheck1+GtrCheck2+GtrCheck3+GtrCheck4+GtrCheck5 = 3

and LsrCheck1+LsrCheck2+LsrCheck3+LsrCheck4+LsrCheck5 = 2

[/sourcecode]

The same case when can be also used directly in a where condition as shown below,

[sourcecode language=”sql” wraplines=”false”]

declare @table table (Col1 int,Col2 int,Col3 int,Col4 int,Col5 int)

insert into @table

select 4, 7, 25, 38, 40 union

select 2, 5, 21, 40, 42 union

select 2, 4, 18, 17, 15 union

select 1, 3, 19, 17, 15 union

select 2, 4, 21, 22, 23 union

select 1, 5, 45, 40, 48 union

select 1, 5, 40, 42, 56

SELECT * FROM @table

WHERE

(case when Col1%2=0 then 1 else 0 end+

case when Col2%2=0 then 1 else 0 end+

case when Col3%2=0 then 1 else 0 end+

case when Col4%2=0 then 1 else 0 end+

case when Col5%2=0 then 1 else 0 end) =3

and

(case when Col1%2=1 then 1 else 0 end +

case when Col2%2=1 then 1 else 0 end +

case when Col3%2=1 then 1 else 0 end +

case when Col4%2=1 then 1 else 0 end +

case when Col5%2=1 then 1 else 0 end ) = 2

and

(case when Col1>19 then 1 else 0 end +

case when Col2>19 then 1 else 0 end +

case when Col3>19 then 1 else 0 end +

case when Col4>19 then 1 else 0 end +

case when Col5>19 then 1 else 0 end ) = 3

and

(case when Col1<20 then 1 else 0 end +

case when Col2<20 then 1 else 0 end +

case when Col3<20 then 1 else 0 end +

case when Col4<20 then 1 else 0 end +

case when Col5<20 then 1 else 0 end ) = 2

[/sourcecode]

**Output:**

Happy Querying 😉