mySQL adding a conditional inside case clause

user_8196779 0
Viewed 35

Essentially I have the following stored procedure that give the right response, but I would like to add additional logic that states if the response for TimeDiff is negative, respond with a 0, otherwise respond with difference (TimeLeft).

BEGIN

SELECT
`Person`,`Code`,
CASE 
WHEN `Problem` = "Large" 
THEN 500 - (TIME_TO_SEC((TimeDiff(Now(),`Start`)/60))) END AS TimeLeft

FROM Table1
WHERE `Code` = "1111"; 

END
mysql sql
1 Answers

You can nest CASE inside CASE:

SELECT
    `Person`,
    `Code`,
    CASE WHEN 
        `Problem` = "Large" 
    THEN 
        CASE WHEN
            500 - (TIME_TO_SEC((TimeDiff(Now(),`Start`)/60))) < 0
        THEN
            0
        ELSE
            500 - (TIME_TO_SEC((TimeDiff(Now(),`Start`)/60))) 
        END
    END AS TimeLeft
FROM Table1
WHERE `Code` = "1111";

Or, you can use the function GREATEST that is specific to MySQL:

SELECT
    `Person`,
    `Code`,
    CASE WHEN 
        `Problem` = "Large" 
    THEN
        GREATEST(500 - (TIME_TO_SEC((TimeDiff(Now(),`Start`)/60))), 0)
    END AS TimeLeft
FROM Table1
WHERE `Code` = "1111";
user_41160170
Related