SQL CASE statement logic

Question

In SQL Developer, I have a column Service Completed which has a date and time but I extracted into hours and minutes only. With the hour and minutes, I used a case statement to tell me 'Yes' or 'No' if it is 24 hours or less - hence if it is 24:01 it will be 'No' since it is 1 minute over.

This is my case statement.

SELECT 
    CASE 
        WHEN (((extract(day FROM service_completed) * 24) + extract(hour FROM service_completed) <= 24)
            AND (extract(minute FROM service_completed)) <= 0)
            THEN 'Y'
            ELSE 'N'
    END AS Completed_within_24HRS

This is the result

18:52   N
17:37   N
47:21   N
20:29   N
45:31   N
23:12   N
29:14   N

The result has several records that should be 'Y' but got 'N' instead. Please help how can I correct the logic in the case statement.

Thanks

Answer 1

Your problem appears to be that you're always checking the minute, and requiring that it be <= 0. I think what you're wanting is anything less than 24:00. That means the only time you want to check the minute is if the hour is exactly 24. Something like this:

case when
  extract(day from service_completed)*24
    + extract(hour from service_completed) < 24
  or (
    extract(day from service_completed)*24 
      + extract(hour from service_completed) = 24
    and extract(minute from service_completed) = 0
  )
then 'Y' else 'N' end as Completed_within_24HRS 

Side note: indenting can really help with writing boolean expressions.

Answer 2

SELECT CASE 
        WHEN extract(day FROM service_completed) * 24 + extract(hour FROM service_completed) < 24
        THEN 'Y'        
        WHEN (extract(day FROM service_completed) * 24 +           extract(hour FROM service_completed) = 24
        AND extract(minute FROM service_completed) = 0)
        THEN 'Y'
        ELSE 'N'
        END AS Completed_within_24HRS

Example for you db<>fiddle