CODEWITHSUNDEEP
sqlsql-server
SouravA
SouravA

Reputation: 5253

Writing case statement based on BETWEEN operator in sql server

I have a CASE statement, which looks like below:

SELECT CASE 
WHEN <expression>> BETWEEN 0 AND 10 THEN '0-10'
WHEN <<expression>> BETWEEN 11 AND 20 THEN '11-20'
WHEN <<expression>> BETWEEN 21 AND 30 THEN '21-30'
...

This works great but it looks ugly.

I want to convert it to something which looks like below:

SELECT CASE <expression>>
WHEN  BETWEEN 0 AND 10 THEN '0-10'
WHEN  BETWEEN 11 AND 20 THEN '11-20'
WHEN  BETWEEN 21 AND 30 THEN '21-30'
...

Obviously the second query looks better as I dont have to write that complex expression multiple times. But it is error-ing out. 

Incorrect syntax near the keyword 'between'.

Can someone please let me know what I am doing wrong here?

UPDATE:

As pointed out, I have changed the query in my question to make the ranges exclusive.

Upvotes: 6

Views: 89487

Answers (7)

Ilya Dorfman
Ilya Dorfman

Reputation: 1

In your particular case one can do the trick below. In general - yes - I am missing this option too.
with tst as (select end_n::bigint , start_n::bigint from (values(100,95),
(100,90),(100,80),(100,70),(100,60),(100,50),(100,40),(100,30))a(end_n,start_n))
select tst.*,(end_n - start_n)/10,
case (end_n - start_n)/10
when 0 then 'bucket_0_10'
when 1 then 'bucket_10_20'
when 2 then 'bucket_10_30'
else 'bucket_other'
end
from tst

But it is static . Simple query with above trick and no "case" would make it dynamic

Upvotes: 0

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5757

Use CROSS/OUTER APPLY for that

DECLARE @a AS TABLE (
a int ,
b INT
);

INSERT INTO @a VALUES (1,2),(4,5),(4,1),(21,32),(32,21),(43,2);


SELECT CASE 
WHEN expr BETWEEN 0 AND 10 THEN '0-10'
WHEN expr BETWEEN 11 AND 20 THEN '11-20'
WHEN expr BETWEEN 21 AND 30 THEN '21-30'
END

FROM @a 
CROSS APPLY (SELECT  CASE WHEN a > b THEN a ELSE b END expr) b

Upvotes: 0

Bohemian
Bohemian

Reputation: 425128

Use a subquery, but also optimize the cases a bit more cleanly:

SELECT ...,
  CASE 
    WHEN calc >= 100 THEN 'Over 100'
    ELSE CONCAT(calc * 10, ' - ', calc * 10 + 9)
  END AS range
FROM (SELECT *, FLOOR(<<expression>> / 10) as calc
     FROM tableName) x

The FLOOR() function rounds down to the nearest integer below the supplied number. By first dividing your expressiin by 10, then flooring, then multiplying by 10, you get the start of the range of the expression. Eg, if the expression result is 63.4, 63.4 --> 6.34 --> 6 --> 60

If there's no requirement for a top-range "Over somevalue", you could omit the CASE entirely, with just CONCAT(calc * 10, ' - ', calc * 10 + 9) which will handle values in any range.

Note there is an error in your intended logic: The ranges should be 0-9, 10-19,... etc. This query addresses that too.

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

With CTE:

;WITH cte as
(
    SELECT <expression>> AS Expression  FROM TableName
)

SELECT CASE
          WHEN Expression BETWEEN 0 AND 10 THEN '0-10'
          WHEN Expression BETWEEN 10 AND 20 THEN '10-20'
          WHEN Expression BETWEEN 20 AND 30 THEN '20-30'
       END AS C
FROM cte

If Expression is always > 0 and intervals are sequential then you can even more shorten it:

SELECT CASE
          WHEN Expression <= 10 THEN '0-10'
          WHEN Expression <= 20 THEN '10-20'
          WHEN Expression <= 30 THEN '20-30'
       END AS C
FROM cte

Upvotes: 3

Ajay2707
Ajay2707

Reputation: 5808

Case statement have 2 variation , both have different thoughs, 

1. This is used when condition is dyanmically change and output also want to change
SELECT CASE 
WHEN <<expression>> or condition1 THEN output1
WHEN <<expression>> or condition2 THEN output2
WHEN <<expression>> or condition3 THEN output3
WHEN <<expression>> or condition4 THEN output4
END

2.  This is used when condition is same , only output is different same like swith case in c#.
SELECT CASE <<expression>> 
when result1 then output1
when result2 then output2
when result3 then output3
when result4 then output4
END

For you question. Give the condition in each when statement.

SELECT CASE 
WHEN  <expression>> BETWEEN 0 AND 10 THEN '0-10'
WHEN  <expression>> BETWEEN 10 AND 20 THEN '10-20'
WHEN  <expression>> BETWEEN 20 AND 30 THEN '20-30'


SELECT CASE 
WHEN 1 = 1 THEN 'YES'  --NEED THE EXPRESSION OF EACH WHEN, IF RESULT IS TRUE, THEN "THEN STATMENT IS OUTPU ELSE NEXT WHEN STATEMENT
WHEN 2 = 2 THEN 'YES'
WHEN 1 = 2 THEN 'NO'
ELSE   --DEFAULT VALUE IF ALLABOVE FAILED
    'NA'
END

Upvotes: 0

jpw
jpw

Reputation: 44891

What you want to is not possible as the syntax for the between statement requires an expression to test as it's first parameter:

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

So you're stuck with the first syntax.

On a side note, your ranges is a bit off as between uses inclusive ranges, but the case expressions are evaluated in order so the ranges are really:

WHEN <<expression>> BETWEEN  0 AND 10 THEN '0-10'
WHEN <<expression>> BETWEEN 11 AND 20 THEN '11-20'
WHEN <<expression>> BETWEEN 21 AND 30 THEN '21-30'

Upvotes: 4

Mudassir Hasan
Mudassir Hasan

Reputation: 28761

You can try using subquery

SELECT CASE 
WHEN ColName BETWEEN 0 AND 10 THEN '0-10'
WHEN ColName BETWEEN 10 AND 20 THEN '10-20'
WHEN ColName BETWEEN 20 AND 30 THEN '20-30'
...

FROM
(
 SELECT <<expression>> as ColName
 FROM tableName
) Z

Upvotes: 0

Related Questions