Get the most recent event in the 24 hour cycle

Question

I have a table get_today_events that shows what events are taking place today. I need to create a view that shows which events are actual (that means that current time is between actual event and the next event according to their time in eventtime column) for each scheduleid and not take into consideration events where is_work column is N. The issue I got stuck in is that condition in this subquery :

select scheduleid
     , max(eventtime) as eventtime
from get_today_events
where cast(current_timestamp as time) >= eventtime
  and is_work = 'Y'
group by scheduleid

works fine until current_timestamp is between 00:00:00 and the first event after 00:00:00 – then it shows nothing. I've found a correct solution but I am looking forward to find a shorter way to achieve the same thing.

1st edit :

To make it clear I am posting an example with expected data for STD scheduleid. There are five time intervals (TI hereinafter) :

--------------------------------------------
scheduleid | eventname | eventtime | is_work
--------------------------------------------
   STD        Suspend    02:00:00       Y
   STD        Start      04:00:00       Y
   STD        Stop       05:00:00       Y
   STD        Suspend    19:00:00       Y
   STD        Start      02:00:00       Y
   STD        Stop       21:00:00       N

• TI between 02:00:00 and 04:00:00 (actual eventname is Suspend);
• TI between 04:00:00 and 05:00:00 (actual eventname is Start);
• TI between 05:00:00 and 19:00:00 (actual eventname is Stop);
• TI between 19:00:00 and 21:00:00 02:00:00 (actual eventname is Start);
• TI between 21:00:00 and 02:00:00 (actual eventname is Stop).

Answer 1

You want to get the most recent event in the 24 hour cycle.

MAX(CASE WHEN CURRENT_TIME >= EVENTTIME THEN EVENTTIME END)

gives the latest time before CURRENT_TIME and NULL if there is none.

You can then use COALESCE to replace that NULL with the last event before midnight.

SELECT
    SCHEDULEID,
    COALESCE(MAX(CASE WHEN CURRENT_TIME >= EVENTTIME THEN EVENTTIME END),
             MAX(EVENTTIME)) as EVENTTIME
FROM GET_TODAY_EVENTS
WHERE IS_WORK = 'Y'
GROUP BY SCHEDULEID

If you want to get the details of these events you can JOIN with the original table.

SELECT mr.SCHEDULEID,mr.EVENTTIME,gte.EVENTTYPEID
  FROM
  (SELECT
      SCHEDULEID,
      COALESCE(MAX(CASE WHEN CURRENT_TIME >= EVENTTIME THEN EVENTTIME END),
               MAX(EVENTTIME)) as EVENTTIME
     FROM GET_TODAY_EVENTS
    WHERE IS_WORK = 'Y'
    GROUP BY SCHEDULEID) AS mr
 JOIN GET_TODAY_EVENTS gte 
   ON (gte.SCHEDULEID=mr.SCHEDULEID AND gte.EVENTTIME=mr.EVENTTIME)

SQLFiddle