sql - mySQL adding a conditional inside case clause

Question

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

question from:https://stackoverflow.com/questions/65867337/mysql-adding-a-conditional-inside-case-clause

Answer 1

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";