Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

SQL Case When Count Then value else value is not giving desired result

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 .

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading