CODEWITHSUNDEEP
mysql
jlig
jlig

Reputation: 241

MySQL Case statement "tweak"

I have the following working query that calculates an employees accrual time in hours, but I need to change the 11 & 14 to be (14 & 18) IF manager=1

Here's my current code:

CASE 
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) < 1
THEN '0'
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) >= 1 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 60
THEN '11'
WHEN TIMESTAMPDIFF(MONTH, hiredate, NOW()) >= 61 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 120
THEN '14'
ELSE '18'    
END AS monthly_Accrual_Level

How do I add in the extra variable "manager=1" to over-ride the following cases:

Upvotes: 1

Views: 45

Answers (2)

Bohemian
Bohemian

Reputation: 425258

You can either use a “sub case”:

CASE
...
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) >= 1 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 60
THEN CASE WHEN manager = 1 THEN '14' ELSE '11' END
... —- similar for other manager value
END AS monthly_Accrual_Level

or add each side of the condition to a WHEN:

CASE
...
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) >= 1 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 60
AND manager = 1 THEN '14'
WHEN TIMESTAMPDIFF(DAY,hiredate,NOW()) >= 1 AND TIMESTAMPDIFF(MONTH, 
hiredate, NOW()) <= 60
AND manager != 1 THEN '11'
... —- similar for other manager value
END AS monthly_Accrual_Level

I like the “sub case” better because it more closely captures your intention as expressed in English, but choose whichever you find easier to read.

Upvotes: 2

jlig
jlig

Reputation: 241

Thanks to Bohemian for the excellent help!

Here is the solution I ended up with:

CASE
    WHEN TIMESTAMPDIFF( DAY, hiredate, NOW( ) ) < 1 THEN '0' WHEN TIMESTAMPDIFF( DAY, hiredate, NOW( ) ) >= 1 

        AND TIMESTAMPDIFF( MONTH, hiredate, NOW( ) ) <= 60 AND manager != 1 THEN '11' WHEN TIMESTAMPDIFF( DAY, hiredate, NOW( ) ) >= 1 
        AND TIMESTAMPDIFF( MONTH, hiredate, NOW( ) ) <= 60 AND manager = 1 THEN '14' WHEN TIMESTAMPDIFF( DAY, hiredate, NOW( ) ) >= 1 

        AND TIMESTAMPDIFF( MONTH, hiredate, NOW( ) ) <= 120 AND manager != 1 THEN '14' WHEN TIMESTAMPDIFF( DAY, hiredate, NOW( ) ) >= 1 
        AND TIMESTAMPDIFF( MONTH, hiredate, NOW( ) ) <= 120 AND manager = 1 THEN '18' 
    ELSE '18' 
END AS monthly_Accrual_Level

Upvotes: 0

Related Questions