SQL, объединяющий информацию о доступности и ограничениях нескольких расписаний

ProgramBox

SQL, объединяющий информацию о доступности и ограничениях нескольких расписаний

Post author:admin
Запись опубликована:25 октября, 2022
Post category:Вопросы по программированию

#sql-server #merge #schedule

#sql-server #слияние #Расписание

Вопрос:

Я работаю над приложением бронирования в SQL Server 2016, где в основной таблице есть временные интервалы, которые можно забронировать для каждого сотрудника. Существует множество других таблиц, которые предоставляют информацию о том, можно ли выполнить бронирование (положительное или отрицательное). Например, может быть таблица доступности персонала (положительная), которая показывает, когда персонал может быть забронирован, и праздничный стол, показывающий, когда персонал не может быть забронирован.

Основная таблица:

 CREATE TABLE [tbl_timeslots]
(
    [slotid] [int] ,
    [fromdate] [datetime] ,
    [todate] [datetime] ,
    [staffid] [int] 
)

Положительная таблица (при наличии персонала)

 CREATE TABLE [tbl_availability]
(
    [staffid] [int] ,
    [fromdate] [datetime] ,
    [todate] [datetime] 
)

Отрицательная таблица (когда персонал недоступен)

 CREATE TABLE [tbl_holidays]
(
    [staffid] [int] ,
    [fromdate] [datetime] ,
    [todate] [datetime] 
)

Задача: найти все временные интервалы из основной таблицы, где персонал доступен, а не в отпуске.

Я ищу способ объединить эти несколько положительных / отрицательных таблиц, чтобы определить, доступен ли конкретный персонал для определенного начального / конечного периода.

Я знаю, что это можно сделать, используя EXISTS / NOT EXISTS, как в примере ниже, но проблема в том, что как только вам нужно перебирать большое количество доступных опций и несколько положительных / отрицательных таблиц, это становится очень медленным.

 select 
    staffid, fromdate, todate 
from 
    tbl_timeslots 
where 
    exists (select staffid from tbl_availability 
            where tbl_availability.staffid = tbl_timeslots.staffid 
              and tbl_availability.fromdate <= tbl_timeslots.fromdate 
              and tbl_availability.todate >= tbl_timeslots.todate) 
    and not exists (select tbl_holidays.staffid from tbl_holidays 
                    where tbl_holidays.staffid = tbl_timeslots.staffid 
                      and tbl_holidays.fromdate < tbl_timeslots.todate 
                      and tbl_holidays.todate > tbl_timeslots.fromdate)

Мой вопрос заключается в том, есть ли лучший способ объединить / объединить все эти положительные / отрицательные таблицы с использованием SQL для создания одной выходной таблицы, показывающей доступность.

1. Пожалуйста, «выполните итерацию по большому числу», определите «большой». а также определите «очень медленно» для этого самого вопроса, основанного на мнениях. Решением было бы обновить таблицу tbl_timeslots с помощью триггеров после обновления одной из таблиц параметров.

2. При поиске доступных временных интервалов большое количество будет означать тысячи вариантов, , но вопрос заключается только в упрощении фактических требований. Например, может существовать таблица ресурсов (отрицательная) с тысячами существующих бронирований, и для каждого проверяемого временного интервала вам нужно будет проверить, что резервирования ресурсов не существует. Что касается триггеров для обновления доступности, это не вариант, поскольку запрос является динамическим, например, один раз для временного интервала потребуется ресурс, а для другого запроса ресурс не понадобится.

3. Если есть тысячи вариантов, то это скорее проблема проектирования.

4. Вопрос с просьбой о помощи с производительностью запросов лучше задавать на dba.stackexchange.com — Но, 2 вопроса к вам: 1) Есть ли у таблиц pos / neg какие-либо индексы? Обе таблицы, показанные в вопросе, значительно улучшили бы производительность, если бы у них был включен индекс staffid, todate . 2) tbl_timeslots Увеличивается ли со временем, оставляя прошлую историю в таблице? Если это так, вы, вероятно, захотите добавить a todate > GETDATE() и добавить индекс todate для повышения производительности.

5. Это просто упрощенные таблицы. Фактические таблицы имеют индексы для staffid, fromdate и todate.

Ответ №1:

Вы можете использовать left join следующее:

 select distinct staffid, fromdate,todate 
  from tbl_timeslots t
Left join tbl_availability a
       On A.staffid=t.staffid and a.fromdate<=t.fromdate and a.todate>=t.todate
Left join tbl_holidays h
       On h.staffid=t.staffid and h.fromdate<t.todate and h.todate >t.fromdate
Where h.staffid is null and a.staffid is not null