I have a table "agenda" like this

And I need, with a select, to get the "free" hours of the agenda like this:

Do you have an idea on how to write the select to get this result?

Already thank you to be interested on my query.

CodePudding user response：

LEAD and CASE-WHEN should do the trick:

select distinct START_H, end_h
from
(
select perid, daynum, start_hour, end_hour, 
    case 
      when (LEAD(start_hour) OVER(order by perid, daynum, start_hour, end_hour)) > end_hour then end_hour
      else NULL
    end as START_H,
    case 
      when (LEAD(start_hour) OVER(order by perid, daynum, start_hour, end_hour)) > end_hour then (LEAD(start_hour) OVER(order by perid, daynum, start_hour, end_hour))
      else NULL
    end as END_H
from agenda
)as sq
WHERE start_h IS NOT NULL 
   and end_h IS NOT NULL

Note: This will work perfectly for the sample data you provided. In case of date change or anything, you may have to update accordingly

DEMO

CodePudding user response：

Summary: You can you use lag function with partition by to simplify the problem and then use timediff to get the end result.

select PREV_END_HOUR as START_H, START_HOUR as END_H
from (
    select *, 
lag(END_HOUR) over(partition by PERID, DAYNUM order by START_HOUR) as PREV_END_HOUR
    from 
    agenda) x
where timediff(START_HOUR,PREV_END_HOUR) > 0

The inner query will return the below result.

 ------- -------- ------------ ---------- --------------- 
| PERID | DAYNUM | START_HOUR | END_HOUR | PREV_END_HOUR |
 ------- -------- ------------ ---------- --------------- 
|   100 |      3 | 08:00:00   | 11:00:00 | NULL          |
|   100 |      3 | 13:00:00   | 15:00:00 | 11:00:00      |
|   100 |      3 | 16:00:00   | 18:00:00 | 15:00:00      |
 ------- -------- ------------ ---------- --------------- 

The inner query partitions by the PERID, DAYNUM to ensure we are not comparing different period or different day. We then order by START_HOUR to ensure the order of classes is preserved for running the lag function in the correct order. We then run lag function and pass it the END_HOUR. This brings the previous class END_HOUR on the same row as the next START_HOUR which make its possible to compare the times.

Now that we have the previous class end hour on the same row we can add a outer query which checks if START_HOUR is greater than the PREV_END_HOUR. The final result is as below.

 ---------- ---------- 
| START_H  | END_H    |
 ---------- ---------- 
| 11:00:00 | 13:00:00 |
| 15:00:00 | 16:00:00 |
 ---------- ----------