Как находя интервал между датами учитывать время только в пределах одного дня?

Question

Необходимо проверить интервал между занятиями у одного тренера. Если перерыв между занятиями < 15 минут или > 3х часов, вывести информацию об этом. Kак я пытался это реализовать:

select
    count(*) into v_cnt3
from lesson l1,
     lesson l2
where l1.lesson_id <> l2.lesson_id
and   l1.lesson_coach_id = l2.lesson_coach_id
and   ((l1.lesson_start_date - l2.lesson_end_date)*24 > 3
or     (l1.lesson_start_date - l2.lesson_end_date)*24*60 < 15)
group by trunc(l1.lesson_start_date);

if v_cnt3 > 0 then
    dbms_output.put_line('У одного или более тренеров интервал между занятиями меньше 15 минут или больше 3 часов');
end if;

Тестовые данные:

with lesson (lesson_id,lesson_section_id,lesson_coach_id,lesson_room,lesson_start_date,lesson_end_date) as (
    select 1,1,1,111,to_date ('16.04.2020 12:30','dd.mm.yyyy hh24:mi'),to_date ('16.04.2020 13:00','dd.mm.yyyy hh24:mi') from dual union all
    select 2,1,1,333,to_date ('16.04.2020 13:35','dd.mm.yyyy hh24:mi'),to_date ('16.04.2020 14:00','dd.mm.yyyy hh24:mi') from dual union all
    select 3,1,1,111,to_date ('16.04.2020 16:00','dd.mm.yyyy hh24:mi'),to_date ('16.04.2020 16:30','dd.mm.yyyy hh24:mi') from dual union all
    select 4,1,1,111,to_date ('16.04.2020 17:00','dd.mm.yyyy hh24:mi'),to_date ('16.04.2020 18:00','dd.mm.yyyy hh24:mi') from dual union all
    select 5,1,1,111,to_date ('16.04.2020 19:00','dd.mm.yyyy hh24:mi'),to_date ('16.04.2020 20:00','dd.mm.yyyy hh24:mi') from dual
)
select * from lesson

Появились проблемы:

Если у этого тренера есть записи о занятиях, которые проходят в разные дни, то данный запрос считает, что интервал между занятиями составляет более трех часов. Пытался пофиксить это с помощью GROUP BY по дате, но не получилось.
Если все записи в пределах одного дня, то часть запроса, проверяющая на интервал более трех часов прекрасно работает. Но с минутами сложнее - вычисляя, сколько минут прошло путем умножения числа часов в интервале на 60, этот запрос работает так, словно между занятиями прошло менее 15 минут, хотя это не так.

Answer 1

Пропущено условие соединение - "один и тот же день". Учитывать надо только занятия - каждое текущее и строго следующее за ним, для этого так же не хватает условия.

Итого:

select l1.*    
from lesson l1
join ( 
    select l.*, 
        lag (lesson_id) over (partition by lesson_coach_id order by lesson_start_date) prev
    from lesson l
) l2 on (
        l2.lesson_coach_id = l1.lesson_coach_id
    and l2.lesson_id <> l1.lesson_id
    and trunc (l2.lesson_start_date) = trunc (l1.lesson_start_date)
    and l2.prev = l1.lesson_id) 
where ((l2.lesson_start_date - l1.lesson_end_date)*24 > 3 or
       (l2.lesson_start_date - l1.lesson_end_date)*24*60 < 15)
/

Условия могут быть сложнее, если тренеры работают по ночам.

Как работает арифметика дат, смотрите так:

select 
    case when trunc (t1) = trunc (t2) then 'same day' else 'other day' end compare,
    (t2 - t1)*24*60 mins
from (select sysdate-1/24 t1, sysdate t2 from dual);

COMPARE         MINS
--------- ----------
same day          60

БЛОГ НА HUSL

Как находя интервал между датами учитывать время только в пределах одного дня?

Ответы (1 шт):