Нередко в практике разработки на платформе 1С возникает задача: в непериодическом регистре сведений хранятся события с датами начала и окончания (ДатаНачала, ДатаОкончания). Мероприятия могут накладываться друг на друга, полностью перекрываться или выходить за границы заданного отчетного периода. Нам необходимо составить запрос, который подсчитает точное количество календарных дней, когда проходило хотя бы одно событие, исключив повторный учет дней перекрытия.
Рассмотрим несколько практических подходов к решению этой задачи: от классического соединения со шкалой дат до чисто математического объединения интервалов.
Разберем самый простой и быстрый с точки зрения написания вариант. Если в конфигурации присутствует производственный календарь (например, регистр сведений ДанныеПроизводственногоКалендаря или аналогичная таблица дат), мы можем использовать его в качестве опорной шкалы.
Посмотрим на логику соединения: выбираем все даты за нужный период и соединяем их с регистром мероприятий по условию попадания даты в интервал проведения.
ВЫБРАТЬ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Календарь.Дата) КАК КоличествоДней
ИЗ
РегистрСведений.ДанныеПроизводственногоКалендаря КАК Календарь
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.Мероприятия КАК Мероприятия
ПО (Календарь.Дата МЕЖДУ НАЧАЛОПЕРИОДА(Мероприятия.ДатаНачала, ДЕНЬ) И НАЧАЛОПЕРИОДА(Мероприятия.ДатаОкончания, ДЕНЬ))
ГДЕ
Календарь.Дата МЕЖДУ &НачалоПериода И &КонецПериода
В этом запросе функция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) гарантирует, что каждый день, на который пришлось хотя бы одно мероприятие, будет учтен ровно один раз.
Проанализируем ситуацию, когда в базе данных нет готового регистра календаря или мы пишем универсальный алгоритм. В языке запросов 1С отсутствуют рекурсивные конструкции или генераторы последовательностей, но мы можем сформировать таблицу чисел декартовым произведением статичных выборок и прибавить их к начальной дате периода.
Разберем по шагам реализацию запроса с генерацией дат:
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d) КАК Период
ПОМЕСТИТЬ ВТ_Периоды
ИЗ
(ВЫБРАТЬ 0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК aa,
(ВЫБРАТЬ 0 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК bb,
(ВЫБРАТЬ 0 КАК c ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК cc,
(ВЫБРАТЬ 0 КАК d ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК dd
ГДЕ
aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)
ИНДЕКСИРОВАТЬ ПО
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_Периоды.Период) КАК КоличествоДней
ИЗ
ВТ_Периоды КАК ВТ_Периоды
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.Мероприятия КАК Мероприятия
ПО (ВТ_Периоды.Период МЕЖДУ НАЧАЛОПЕРИОДА(Мероприятия.ДатаНачала, ДЕНЬ) И НАЧАЛОПЕРИОДА(Мероприятия.ДатаОкончания, ДЕНЬ))
Такой подход формирует временную таблицу всех дней заданного диапазона (в данном примере до 10 000 дней), после чего выполняет связь с реальными интервалами.
Рассмотрим более сложный алгоритмический метод, не требующий генерации строк на каждый день периода. Он работает за счет нахождения непрерывных блоков занятости («островов») через поиск их левых и правых границ:
Посмотрим на пример реализации с предварительным обрезанием интервалов под границы отчета:
ВЫБРАТЬ
ВЫБОР
КОГДА Мероприятия.ДатаНачала < &НачалоПериода ТОГДА &НачалоПериода
ИНАЧЕ НАЧАЛОПЕРИОДА(Мероприятия.ДатаНачала, ДЕНЬ)
КОНЕЦ КАК ДатаНачала,
ВЫБОР
КОГДА Мероприятия.ДатаОкончания > &КонецПериода ТОГДА &КонецПериода
ИНАЧЕ НАЧАЛОПЕРИОДА(Мероприятия.ДатаОкончания, ДЕНЬ)
КОНЕЦ КАК ДатаОкончания
ПОМЕСТИТЬ ВТ_ОбрезанныеИнтервалы
ИЗ
РегистрСведений.Мероприятия КАК Мероприятия
ГДЕ
Мероприятия.ДатаОкончания >= &НачалоПериода
И Мероприятия.ДатаНачала <= &КонецПериода
ИНДЕКСИРОВАТЬ ПО
ДатаНачала,
ДатаОкончания
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
СУММА(Границы.Разность) КАК ЗанятыхДней
ИЗ
(ВЫБРАТЬ РАЗЛИЧНЫЕ
1 - РАЗНОСТЬДАТ(ДАТАВРЕМЯ(1, 1, 1), Дано.ДатаНачала, ДЕНЬ) КАК Разность
ИЗ
ВТ_ОбрезанныеИнтервалы КАК Дано
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОбрезанныеИнтервалы КАК Левее
ПО (Левее.ДатаНачала < Дано.ДатаНачала)
И (Дано.ДатаНачала <= ДОБАВИТЬКДАТЕ(Левее.ДатаОкончания, ДЕНЬ, 1))
ГДЕ
Левее.ДатаНачала ЕСТЬ NULL
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ РАЗЛИЧНЫЕ
РАЗНОСТЬДАТ(ДАТАВРЕМЯ(1, 1, 1), Дано.ДатаОкончания, ДЕНЬ)
ИЗ
ВТ_ОбрезанныеИнтервалы КАК Дано
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОбрезанныеИнтервалы КАК Правее
ПО (Правее.ДатаНачала <= ДОБАВИТЬКДАТЕ(Дано.ДатаОкончания, ДЕНЬ, 1))
И (Дано.ДатаОкончания < Правее.ДатаОкончания)
ГДЕ
Правее.ДатаОкончания ЕСТЬ NULL) КАК Границы
Выясним ключевые моменты, которые нужно учитывать при расчетах:
23:59:59). Для корректного расчета дней границы всегда нужно приводить к началу дня функцией НАЧАЛОПЕРИОДА(..., ДЕНЬ).ДОБАВИТЬКДАТЕ(..., ДЕНЬ, 1).ИНДЕКСИРОВАТЬ ПО по полям дат, чтобы избежать замедления работы СУБД.