I have the following situation. Sample data:

What I am trying is the folowing:

When the Datetime value in the row of column OCTeamLastTime is higher dan the current datetime CURRENT_TIMESTAMP I want a SQL variable to be Now otherwise use the value from OCTeamLastTime as CanDoOC.

I am currently trying the folowing statement. But this gives me the result Now for every outcome.

SELECT OCTeamName, OCTeamID, OCTeamLastTime, (
    CASE WHEN(SELECT COUNT(OCTeamID) FROM OCTable WHERE OCTeamLastTime >= CURRENT_TIMESTAMP AND OCTeamID = OCTeamID) > 0 THEN 'Now' ELSE OCTeamLastTime END
) AS CanDoOC
FROM OCTable

I want to use these results in a option list in php/html. Desired results based on above table:

<option data-subtext="Next OC: <?php echo $SelectOCTeam['CanDoOC']; ?>" value="<?php echo $SelectOCTeam['OCTeamID']; ?>"><?php echo $SelectOCTeam['OCTeamName']; ?></option>

It should create 5 options in this case. With Next OC: Now for the team with ID 1,3 and 5 and the OCTeamLastTime value for the other options.

Does anyone how to accomplish this? As what I did is not working and I cant think of any other way to make this work.

CodePudding user response：

I believe that you just want the following query (without subquery)

SELECT OCTeamName,OCTeamID,OCTeamLastTime, 
    CASE WHEN OCTeamLastTime >= CURRENT_TIMESTAMP 
      THEN 'Now' 
      ELSE OCTeamLastTime 
      END AS CanDoOC 
FROM OCTable

CodePudding user response：

You can use CASE expression for this:

SELECT CASE
    WHEN OCTeamLastTime < CURRENT_TIMESTAMP THEN 'Now'
    ELSE OCTeamLastTime
END AS CanDoOC
FROM ...

CodePudding user response：

Only slight modification to your query will help you get desired output

SELECT OCTeamName,OCTeamID,OCTeamLastTime, (CASE WHEN OCTeamLastTime >= CURRENT_TIMESTAMP THEN 'Now' ELSE OCTeamLastTime END) AS CanDoOC FROM OCTable