T-SQL функция рассчета количества рабочих часов между двумя датами с учетом бизнес-календаря

Итак, готова функция расчета количества рабочих часов между двумя датами. Данную функцию мы сами используем в отчетах по поддержке.

Прежде расскажу как хранятся данные в бизнес-календаре.
Бизнес календарь имеет 3 секции (лучше посмотреть в CardManager)

1. Основная информация (только одно поле Name, понятно для чего оно нужно)
2. Года (Years). Имеет отдно поле Year в котором указывается год
2.1 Подсекция Days. Имеет поля Day - номер дня от начала года и Type тип
2.1.1 Подсекция WorkTime. Поля StartTime и EndTime - временной диапазон
3. DefaultTimeSettings. Поля StartTime и EndTime - временной диапазон


В календаре можно делать несколько настроек.

1. Настрока рабочего времени по умолчанию.
Данная настройка будет храниться в секции DefaultTimeSettings. Строки секции будут содержать временные интервалы.

2. Настройка конкретного дня.
В этом случае будет создана строка в секции Years, в подсекции Days, в подсекции WorkTime с указанием интервала.
Например, 02.01.2009 должен быть рабочим днем, с рабочим времением с 12:00 до 15:00.
При сохранении данные будут хранится следующим образом (названия секций в квадратных скобках):
[Years]: Year = 2009
[Days]: Day = 2
[WorkTime]: StartTime=12:00, EndTime=15:00

3. Настройка календаря по умолчанию
Данные настройки будут храниться в той же секции Years (аналогично п.2), но в году 1796.

Если для конкретного дня нет сохраненных настроек, берется следующее рабочее время:
Рабочие дни: с понедельника по пятницу
Рабочий график: с 9:00 до 13:00, и с 14:00 до 18:00


Скрипт состоит из 3х функций
1. Проверка, является ли данный день рабочим FIsWorkTime
2. Ф-ция рассчета длительности рабочего времени для одного дня FGetDayDuration
3. Основная ф-ция рассчета длительности между двумя датами FBusinessHours2

Алгоритм следующий.
Ф-ция FBusinessHours2 вычисляет при помощи FGetDayDuration длительность рабочего времени в первом дне интервала (до конца первого рабочего дня). Длительность рабочего времени в последнем дне интервала (от начала рабочего дня до конечного времени), и длительность во всех днях между начальным и конечным.

Поскольку данные хранятся для конкретных дней, а не для всех, то единственным способом подсчета является перебор всех дней в цикле. Это крайне не оптимальный способ. Альтернативным вариантом было бы периодическое создание "карты" рабочего расписания для абсолютно всех дней в интервале, с хранением времени в UTC в отдельной таблице. Тогда расчет длительности можно будет выполнить несколькими SELECT'ами за доли секунды.

Ф-ция FBusinessHours2 может возвращать данные в минутах или часах в зависимости от 3-го параметра @Mode.

Скачать скрипт

Новая версия скрипта Читать дальше