Archive

Archive for October, 2012

T-SQL : An Interesting Question – 1

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,


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

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

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

Output:

image

 

Happy Querying 😉

%d bloggers like this: