I have the folowing situation.
Table (example)
| OCTeamID(type is interger) | OCTeamLastTime( type is datetime) |
|---|---|
| 1 | 1970-01-01 00:00:00 |
| 2 | 2022-01-21 00:37:23 |
| 3 | 1970-01-01 00:00:00 |
| 4 | 2022-01-21 00:37:23 |
| 5 | 1970-01-01 00:00:00 |
What I am trying is the folowing. When the Datetime value in the row of column OCTeamLastTime(datetime) is higher dan the current datetime CURRENT_TIMESTAMP. I want a Sql variable to be Now and else the value from OCTeamLastTime(datetime) 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.
>Solution :
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