CASE WHEN does not return expected value

Question

I have a table with a column that contian birth dates (bdate) and a later date (entry). When a a persons age is lower than 35 I want a value of 2 When the age is >= 36 and <= 40 I want a value of 3, and so on.

However, my query never return a value higher than 3. What is wrong?

SELECT TIMESTAMPDIFF( YEAR, str_to_date( CONCAT( 19, bdate ) , '%Y %m %d' ) , entry),
CASE
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 35 THEN 2
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 36 <= 40 THEN 3
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 41 <= 45 THEN 4
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 46 <= 50 THEN 5
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 51 <= 55 THEN 6
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 56 <= 60 THEN 7
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) >= 61 <= 65 THEN 8
ELSE 0
END AS value
FROM mytable

`>= 36 <= 40` condition tests have error ... should be like `a >= 36 AND a <= 40` — Tun Zarni Kyaw, Feb 28 '14 at 07:11

score 2 · Accepted Answer · answered Feb 28 '14 at 07:19

2

You need to separate the inequalities. Here is an example:

SELECT @a := TIMESTAMPDIFF( YEAR, str_to_date( CONCAT( 19, bdate ) , '%Y %m %d' ) , entry),
CASE
    WHEN @a <= 35 THEN 2
    WHEN @a >= 36 and @a <= 40 THEN 3
    WHEN @a >= 41 and @a <= 45 THEN 4
    WHEN @a >= 46 and @a <= 50 THEN 5
    WHEN @a >= 51 and @a <= 55 THEN 6
    WHEN @a >= 56 and @a <= 60 THEN 7
    WHEN @a >= 61 and @a <= 65 THEN 8
    ELSE 0
END AS value
FROM mytable

That little trick with the temp variable (@a) can save you a lot of typewriting (and make things a bit clearer).

Hope this helps

answered Feb 28 '14 at 07:19

Barranka

20,547
13
65
83

Your solutions was realy a beauty. At first something went wrong during my copy paste, but after fixing that it went out really well. Thank you for open my eyes to the temp variables. – Vercingetorix Feb 28 '14 at 08:09
@Vercingetorix I'm happy to help. Temp variables are one of the hidden jewels of MySQL... if you are careful and creative, they can do amazing things for you (I frequently use them to calculate "running totals" and "moving averages"; take a look to [this post](http://stackoverflow.com/questions/17664436/cumulative-sum-over-a-set-of-rows-in-mysql)) – Barranka Feb 28 '14 at 14:15

score 1 · Answer 2 · answered Feb 28 '14 at 07:20

Remove the greater than part:

SELECT TIMESTAMPDIFF( YEAR, str_to_date( CONCAT( 19, bdate ) , '%Y %m %d' ) , entry),
CASE
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 35 THEN 
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 40 THEN 
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 45 THEN 
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 50 THEN 
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 55 THEN 
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 60 THEN 
WHEN TIMESTAMPDIFF(YEAR, str_to_date(CONCAT(19, bdate), '%Y %m %d'), entry) <= 65 THEN 
ELSE 0
END AS value
FROM mytable

It is looking at >= 36 and evaluating that to true. You cannot simply concatenate like in >= 36 <= 40.

score 0 · Answer 3 · answered Feb 28 '14 at 07:17

There is a syntax error.

This is wrong to compare with two values.

WHEN TIMESTAMPDIFF( YEAR, str_to_date( CONCAT( 19, bdate ), '%Y %m %d' ), entry )
       >= 36 <= 40 THEN 3

Use between clause for comparing a value within a range.

WHEN TIMESTAMPDIFF( YEAR, str_to_date( CONCAT( 19, bdate ), '%Y %m %d' ), entry )
     BETWEEN 36 AND 40 THEN 3

Apply the same on other such comparisons.

score 0 · Answer 4 · answered Feb 28 '14 at 07:18

just subtract values

SELECT
CASE
WHEN bdate - entry <= 35 THEN 2
WHEN bdate - entry between 36 and 40 THEN 3
WHEN bdate - entry between 41 and 45 THEN 4
WHEN bdate - entry between 46 and 50 THEN 5
WHEN bdate - entry between 51 and 55 THEN 6
WHEN bdate - entry between 56 and 60 THEN 7
WHEN bdate - entry between 61 and 65 THEN 8
ELSE 0
END AS value
FROM mytable

CASE WHEN does not return expected value

4 Answers4