Блог

Главная - Вопросы по программированию - Извлечение доступных временных интервалов из запланированного временного интервала

Извлечение доступных временных интервалов из запланированного временного интервала

#sql #oracle

#sql #Oracle

Вопрос:

Я пытаюсь создать представление, которое будет выводить мне все доступные временные интервалы из таблицы календаря планирования, имея уже забронированные временные интервалы в другой таблице.

Учитывая таблицы:

 Table Calendar

ID    Date         StartTime    EndTime
56    18-OCT-16    10.00.00     18.00.00
62    21-OCT-16    11.00.00     20.30.00
72    27-OCT-16    09.30.00     17.00.00
72    28-OCT-16    08.40.00     18.00.00

Table ScheduledTimes

ID    Date         StartTime    EndTime
62    21-OCT-16    13.00.00     14.30.00
62    21-OCT-16    16.00.00     17.00.00
62    21-OCT-16    17.20.00     18.00.00
72    27-OCT-16    09.30.00     10.00.00
72    27-OCT-16    10.00.00     11.00.00
72    28-OCT-16    09.41.00     11.00.00
72    28-OCT-16    12.40.00     18.00.00

Я ищу способ добиться этого:

 ID    Date         StartTime    EndTime
56    18-OCT-16    10.00.00     18.00.00
62    21-OCT-16    11.00.00     13.00.00
62    21-OCT-16    14.30.00     16.00.00
62    21-OCT-16    17.00.00     17.20.00
62    21-OCT-16    18.00.00     20.30.00
72    27-OCT-16    11.00.00     17.00.00
72    28-OCT-16    08.40.00     09.41.00
72    28-OCT-16    11.00.00     12.40.00

Все значения в ScheduledTimes проверены, они должны находиться внутри временных рамок календарного времени и не конфликтовать друг с другом.

Комментарии:

1. Каков тип данных Date, startTime и EndTime? Все ли они хранятся в виде строк? Кроме того, я предполагаю, что идентификаторы имеют смысл (например, номер комнаты, место проведения и т.д.)?

Ответ №1:

Предполагая, что все ваши входные столбцы представляют собой строки:

 with
     calendar ( id, dt, starttime, endtime ) as (
       select 56, '18-OCT-16', '10.00.00', '18.00.00' from dual union all
       select 62, '21-OCT-16', '11.00.00', '20.30.00' from dual union all
       select 72, '27-OCT-16', '09.30.00', '17.00.00' from dual union all
       select 72, '28-OCT-16', '08.40.00', '18.00.00' from dual
     ),
     scheduledtimes ( id, dt, starttime, endtime ) as (
       select 62, '21-OCT-16', '13.00.00', '14.30.00' from dual union all
       select 62, '21-OCT-16', '16.00.00', '17.00.00' from dual union all
       select 62, '21-OCT-16', '17.20.00', '18.00.00' from dual union all
       select 72, '27-OCT-16', '09.30.00', '10.00.00' from dual union all
       select 72, '27-OCT-16', '10.00.00', '11.00.00' from dual union all
       select 72, '28-OCT-16', '09.41.00', '11.00.00' from dual union all
       select 72, '28-OCT-16', '12.40.00', '18.00.00' from dual
     ),
     u ( id, dt, startdatetime, enddatetime ) as (
       select id, dt, to_date(dt || starttime, 'dd-MON-yyhh24.mi.ss'),
                      to_date(dt || endtime  , 'dd-MON-yyhh24.mi.ss')
         from scheduledtimes
       union all
       select id, dt, null, to_date(dt || starttime, 'dd-MON-yyhh24.mi.ss')
         from calendar
       union all
       select id, dt, to_date(dt || endtime, 'dd-MON-yyhh24.mi.ss'), null
         from calendar
     ),
     prep ( id, dt, starttime, endtime ) as (
       select id, dt, to_char(enddatetime, 'hh24:mi:ss') as starttime,
              to_char(lead(startdatetime) over (partition by id, dt 
                           order by enddatetime), 'hh24:mi:ss') as endtime
       from   u
     )
select id, dt, starttime, endtime
from   prep
where  starttime < endtime
order by id, dt, endtime;
Метки: