Как в запросе 1С сопоставить период отчета с периодом действия договора?

При разработке отчетов в системе 1С:Предприятие программисты регулярно сталкиваются с задачей сопоставления двух временных интервалов. Типичный пример: в отчете за определенный период (например, месяц или квартал) необходимо вывести данные по договорам, которые действовали в этом интервале времени. При этом сам период действия договора задается двумя реквизитами — ДатаНачала и ДатаОкончания.

В этой статье мы подробно разберем, как правильно составить условие в запросе, чтобы учесть все возможные варианты пересечения периодов, как избежать снижения производительности базы данных и как корректно настроить Систему компоновки данных (СКД) для работы со стандартными периодами.

Анализ взаимного расположения периодов (6 сценариев)

Прежде чем переходить к написанию кода запроса, давайте визуализируем ось времени. Предположим, у нас есть период отчета, ограниченный параметрами &НачалоПериода и &КонецПериода, и период действия договора, ограниченный полями ДатаНачала и ДатаОкончания. Существует ровно 6 вариантов их взаимного расположения:

1. Договор завершился до начала периода отчета: период действия полностью находится «левее» на временной шкале. Такие данные попадать в отчет не должны.
2. Договор начался до периода отчета, но закончился внутри него: имеется частичное пересечение. Запись должна попасть в отчет.
3. Договор полностью укладывается внутрь периода отчета: полное пересечение. Запись должна попасть в отчет.
4. Договор начался внутри периода отчета, а закончился после его завершения: частичное пересечение. Запись должна попасть в отчет.
5. Договор начнется только после окончания периода отчета: период действия находится полностью «правее». Такие данные в отчет попадать не должны.
6. Договор начался до начала периода отчета и закончится после его окончания: период отчета полностью поглощается периодом действия договора. Запись обязательно должна попасть в отчет.

Многие начинающие разработчики пытаются решить эту задачу громоздкими конструкциями с использованием логического оператора ИЛИ и оператора МЕЖДУ. Однако существует изящное математическое правило для определения пересечения любых двух отрезков.

Математически оптимальная формула пересечения периодов

Чтобы два временных интервала пересекались (хотя бы частично), должно выполняться простое и лаконичное условие: дата начала первого интервала должна быть меньше или равна дате окончания второго интервала, а дата окончания первого интервала должна быть больше или равна дате начала второго интервала.

Применительно к нашей задаче это условие в запросе 1С будет выглядеть следующим образом:

ГДЕ
    Договор.ДатаНачала <= &КонецПериода
    И Договор.ДатаОкончания >= &НачалоПериода

Рассмотрим подробнее, почему эта формула идеальна:

• Простота чтения и поддержки: всего две строки кода вместо сложных вложенных условий.
• Производительность СУБД: СУБД (будь то MS SQL Server, PostgreSQL или другая поддерживаемая база данных) эффективно оптимизирует простые операции сравнения «больше-меньше» и может использовать существующие индексы по полям дат. Конструкции с ИЛИ, напротив, часто приводят к сканированию всей таблицы (Table Scan).

Подводный камень: Обработка пустых дат (открытые интервалы)

В реальной бизнес-практике договоры часто бывают бессрочными. В таких случаях реквизит ДатаОкончания остается незаполненным. В платформе 1С пустая дата по умолчанию соответствует значению 01.01.0001 00:00:00 (так называемая «пустая дата» или ДАТАВРЕМЯ(1, 1, 1)).

Если мы применим базовую формулу к бессрочному договору, условие Договор.ДатаОкончания >= &НачалоПериода вернет ложь, так как пустая дата начала нашей эры заведомо меньше любой даты отчета. В результате действующий бессрочный договор не попадет в выборку.

Чтобы избежать этой ошибки, мы должны модернизировать наше условие, добавив явную проверку на пустую дату окончания:

ГДЕ
    Договор.ДатаНачала <= &КонецПериода
    И (Договор.ДатаОкончания >= &НачалоПериода 
       ИЛИ Договор.ДатаОкончания = ДАТАВРЕМЯ(1, 1, 1))

Связывание параметров «Стандартного периода» в СКД

Если ваш отчет строится с использованием Системы компоновки данных (СКД), то пользователю гораздо удобнее выбирать период работы через один стандартный интерактивный элемент — СтандартныйПериод (содержащий сразу дату начала и дату окончания).

Давайте разберем по шагам, как правильно связать этот пользовательский параметр с параметрами нашего запроса &НачалоПериода и &КонецПериода:

1. Откройте схему компоновки данных и перейдите на вкладку Параметры.
2. Добавьте новый параметр с именем, например, ПериодОтчета и укажите для него тип данных СтандартныйПериод. Включите для него флаг «Быстрый доступ» или выведите в пользовательские настройки.
3. Для системных параметров НачалоПериода и КонецПериода (которые используются непосредственно в тексте вашего запроса) установите флаг Ограничение доступности. Это скроет их от пользователя, чтобы не перегружать интерфейс.
4. В колонке Выражение для параметра НачалоПериода введите выражение: &ПериодОтчета.ДатаНачала.
5. В колонке Выражение для параметра КонецПериода введите выражение: &ПериодОтчета.ДатаОкончания.

Важное примечание: СКД автоматически приводит значение &ПериодОтчета.ДатаОкончания к концу последней секунды выбранного дня (23:59:59). Это избавляет разработчика от необходимости вручную прописывать функцию КОНЕЦПЕРИОДА() в запросе.

Оптимизация запроса: Избегаем обращения через точку (implicit JOIN)

В обсуждении на форуме упоминается структура: «в запросе этого отчета есть таблица непериодического регистра сведений, у этого регистра есть регистратор документ Договор у этого документа есть период...».

Обращение в условиях запроса вида Регистр.Регистратор.Договор.ДатаНачала является серьезной ошибкой проектирования. Поле Регистратор имеет составной тип данных. Когда СУБД выполняет запрос с разыменованием полей через составной регистратор, она создает неявные левые соединения (LEFT JOIN) абсолютно со всеми таблицами документов, которые могут выступать регистраторами для данного регистра. На больших базах данных такой запрос будет выполняться крайне медленно.

Проанализируем ситуацию и выделим два правильных способа решения этой проблемы:

Способ 1. Использование реквизитов или измерений регистра

Если это возможно на уровне архитектуры конфигурации, добавьте ссылку на договор непосредственно в измерения или реквизиты регистра сведений (например, измерение Договор). Тогда обращение к датам будет идти напрямую через один уровень связи, что значительно повысит скорость работы:

ВЫБРАТЬ
    Регистр.Регистратор,
    Регистр.Договор
ИЗ
    РегистрСведений.СведенияОДоговорах КАК Регистр
ГДЕ
    Регистр.Договор.ДатаНачала <= &КонецПериода
    И (Регистр.Договор.ДатаОкончания >= &НачалоПериода 
       ИЛИ Регистр.Договор.ДатаОкончания = ДАТАВРЕМЯ(1, 1, 1))

Способ 2. Предварительный отбор во временную таблицу

Если данные о периодах действия договоров хранятся исключительно в шапке документов «Договор», лучшим решением по производительности будет предварительный отбор нужных договоров во временную таблицу с последующим соединением с регистром:

// Шаг 1: Выбираем только те договоры, которые попадают в период отчета
ВЫБРАТЬ
    Договор.Ссылка КАК Договор
ПОМЕСТИТЬ ВТ_ДействующиеДоговоры
ИЗ
    Документ.Договор КАК Договор
ГДЕ
    Договор.ДатаНачала <= &КонецПериода
    И (Договор.ДатаОкончания >= &НачалоПериода 
       ИЛИ Договор.ДатаОкончания = ДАТАВРЕМЯ(1, 1, 1))

ИНДЕКСИРОВАТЬ ПО
    Договор
;

// Шаг 2: Соединяем отфильтрованные договоры с таблицей регистра
ВЫБРАТЬ
    Регистр.Регистратор,
    ВТ_ДействующиеДоговоры.Договор
ИЗ
    ВТ_ДействующиеДоговоры КАК ВТ_ДействующиеДоговоры
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.СведенияОДоговорах КАК Регистр
        ПО ВТ_ДействующиеДоговоры.Договор = Регистр.Договор

Использование временной таблицы ВТ_ДействующиеДоговоры позволяет СУБД сначала максимально сузить выборку по проиндексированным полям дат документов, а затем быстро выполнить внутреннее соединение (INNER JOIN) только по тем записям регистра, которые действительно актуальны для текущего отчета.