Почему итоги в запросе 1С становятся неверными при добавлении функции РАЗНОСТЬДАТ?

← К списку

Мы часто сталкиваемся с ситуациями, когда, казалось бы, безобидное добавление функции в запрос приводит к неожиданным и неверным результатам, особенно в части итогов. Сегодня мы разберем одну из таких проблем, связанную с использованием функции РАЗНОСТЬДАТ в запросах 1С, и предложим несколько эффективных решений.

Представьте ситуацию: у вас есть запрос, который корректно выводит остатки и обороты по взаиморасчетам. Итоги по остатку рассчитываются верно. Но стоит только добавить в выборку поле, вычисляющее разницу дат с помощью функции РАЗНОСТЬДАТ, как общие итоги начинают «чудить», показывая неверные значения. Давайте выясним, почему это происходит и как с этим бороться.

Анализируем исходную проблему

Рассмотрим типовой запрос, с которым возникла проблема. В нем мы выбираем данные из регистра накопления ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты:


ВЫБРАТЬ 
    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период КАК Период, 
    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Регистратор КАК Регистратор, 
    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Контрагент КАК Контрагент, 
    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.ДоговорКонтрагента КАК ДоговорКонтрагента, 
    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.СуммаУпрКонечныйОстаток КАК Остаток, 
    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.СуммаВзаиморасчетовПриход КАК Приход 
    //, РАЗНОСТЬДАТ(ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период, &ДатаКон, ДЕНЬ) КАК ДнейПросрочки
ИЗ 
    РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты( 
                , 
                &ДатаКон, 
                Регистратор, 
                , 
                Контрагент В (&Контрагент)) КАК ВзаиморасчетыСКонтрагентамиОстаткиИОбороты
ИТОГИ 
    СУММА(Остаток) 
ПО 
    Контрагент, 
    ДоговорКонтрагента

Как мы видим, если строка с функцией РАЗНОСТЬДАТ закомментирована, итоги по полю Остаток рассчитываются корректно. Но стоит только раскомментировать ее, и общая сумма по Остаток становится неверной. Более того, в некоторых случаях система может брать остаток по первому документу и подставлять его в итог, что совершенно недопустимо.

Выясняем причины некорректных итогов

Давайте проанализируем, почему добавление РАЗНОСТЬДАТ так сильно влияет на расчет итогов. Здесь может быть несколько причин:

Проблема с "датами начала времен" (ДАТАВРЕМЯ(1, 1, 1))

Одной из наиболее распространенных причин некорректных результатов или даже ошибок переполнения при использовании РАЗНОСТЬДАТ является наличие в данных "пустых" дат или дат "начала времен" (01.01.0001). Если в поле ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период встречаются такие даты, попытка вычислить разницу в днях между текущей датой и 01.01.0001 может привести к получению очень большого числа. Это может вызвать переполнение или некорректную обработку на уровне СУБД или платформы 1С, что, в свою очередь, "ломает" расчет итогов.
Особенности работы СУБД PostgreSQL

Мы обратим внимание на упоминания о том, что проблемы с некорректными итогами при использовании РАЗНОСТЬДАТ чаще наблюдались на серверах под управлением PostgreSQL. Функция РАЗНОСТЬДАТ в 1С не имеет прямого аналога в PostgreSQL и транслируется платформой в специфические SQL-конструкции. Различия в механизмах трансляции и выполнения запросов в PostgreSQL могут влиять на правильность вычисления итогов, особенно при сложных запросах с агрегатными функциями и функциями работы с датами.
Влияние на агрегацию

Иногда добавление сложной функции, такой как РАЗНОСТЬДАТ, в выборку может непреднамеренно изменить способ группировки или агрегации данных на более низком уровне, даже если мы явно не указываем ее в секции ИТОГИ ПО. Это может привести к тому, что система будет агрегировать данные не так, как мы ожидаем, вызывая неверные итоги.

Эффективные решения проблемы

Теперь, когда мы понимаем возможные причины, давайте рассмотрим, как можно решить эту проблему.

1. Условный расчет РАЗНОСТЬДАТ для исключения "дат начала времен"

Это решение, найденное нашими коллегами на форуме, оказалось наиболее эффективным для устранения проблемы с ДАТАВРЕМЯ(1, 1, 1). Мы будем использовать конструкцию ВЫБОР КОГДА ... ТОГДА ... КОНЕЦ, чтобы исключить "даты начала времен" из расчета РАЗНОСТЬДАТ.

Принцип работы: Мы проверяем, является ли Период "датой начала времен" (ДАТАВРЕМЯ(1, 1, 1)). Если это так, мы не вычисляем РАЗНОСТЬДАТ для этого поля. В противном случае, расчет производится как обычно. Это предотвращает возникновение очень больших чисел или ошибок, которые могут повлиять на итоговые расчеты.

Пример кода:


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

Объяснение: Добавив условие ВЫБОР КОГДА ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период <> ДАТАВРЕМЯ(1, 1, 1), мы гарантируем, что функция РАЗНОСТЬДАТ будет вызываться только для корректных дат. Если Период равен "дате начала времен", поле ДнейПросрочки для этой строки будет иметь значение NULL, что не повлияет на расчет итогов по другим полям.

2. Разделение логики запроса с использованием пакетных запросов или временных таблиц

В некоторых сложных случаях, особенно при работе с СУБД PostgreSQL, разделение запроса на несколько частей может помочь обойти проблемы с некорректными итогами. Этот подход позволяет сначала вычислить все необходимые промежуточные данные, включая РАЗНОСТЬДАТ, а затем уже выполнять агрегацию.

Принцип работы: Мы можем использовать пакетный запрос, где в первом запросе формируем временную таблицу с рассчитанной РАЗНОСТЬДАТ, а во втором запросе уже работаем с этой временной таблицей, выполняя агрегацию и итоговые расчеты.

Пример концепции:

Первый запрос (Временная таблица): Выбираем все необходимые поля, включая условное вычисление ДнейПросрочки, и помещаем их во временную таблицу (например, ВТ_ДанныеСДнямиПросрочки).


ВЫБРАТЬ 
    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период КАК Период, 
    // ... другие поля ...
    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.СуммаУпрКонечныйОстаток КАК Остаток, 
    ВЫБОР 
        КОГДА ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период <> ДАТАВРЕМЯ(1, 1, 1) 
        ТОГДА РАЗНОСТЬДАТ(ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период, &ДатаКон, ДЕНЬ) 
    КОНЕЦ КАК ДнейПросрочки
ПОМЕСТИТЬ ВТ_ДанныеСДнямиПросрочки
ИЗ 
    РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты(...) КАК ВзаиморасчетыСКонтрагентамиОстаткиИОбороты
;

Второй запрос (Основной запрос с итогами): Выбираем данные из временной таблицы и выполняем агрегацию.


ВЫБРАТЬ 
    ВТ_ДанныеСДнямиПросрочки.Контрагент, 
    ВТ_ДанныеСДнямиПросрочки.ДоговорКонтрагента, 
    ВТ_ДанныеСДнямиПросрочки.Остаток, 
    ВТ_ДанныеСДнямиПросрочки.ДнейПросрочки
ИЗ 
    ВТ_ДанныеСДнямиПросрочки КАК ВТ_ДанныеСДнямиПросрочки
ИТОГИ 
    СУММА(Остаток) 
ПО 
    Контрагент, 
    ДоговорКонтрагента

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

3. Постобработка результата запроса в коде 1С

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

Принцип работы: Запрос выбирает все необходимые поля, кроме ДнейПросрочки. После выполнения запроса и получения выборки или таблицы значений, мы обходим ее в цикле и для каждой строки рассчитываем разность дат, записывая результат в новое поле.

Пример кода 1С:


Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ 
    |    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.Период КАК Период, 
    |    // ... другие поля ...
    |    ВзаиморасчетыСКонтрагентамиОстаткиИОбороты.СуммаУпрКонечныйОстаток КАК Остаток
    |ИЗ 
    |    РегистрНакопления.ВзаиморасчетыСКонтрагентами.ОстаткиИОбороты( 
    |                , 
    |                &ДатаКон, 
    |                Регистратор, 
    |                , 
    |                Контрагент В (&Контрагент)) КАК ВзаиморасчетыСКонтрагентамиОстаткиИОбороты
    |ИТОГИ 
    |    СУММА(Остаток) 
    |ПО 
    |    Контрагент, 
    |    ДоговорКонтрагента";

Запрос.УстановитьПараметр("ДатаКон", ТекущаяДата());
// ... другие параметры ...

РезультатЗапроса = Запрос.Выполнить();
ВыборкаДетальныеЗаписи = РезультатЗапроса.Выбрать();

ТаблицаРезультат = РезультатЗапроса.Выгрузить(); // Или работаем с выборкой напрямую
ТаблицаРезультат.Колонки.Добавить("ДнейПросрочки", Новый ОписаниеТипов("Число"));

Для Каждого СтрокаТЗ Из ТаблицаРезультат Цикл
    Если СтрокаТЗ.Период <> Дата(1, 1, 1) Тогда
        СтрокаТЗ.ДнейПросрочки = РазностьДат(СтрокаТЗ.Период, ТекущаяДата(), ВидПериода.День);
    Иначе
        СтрокаТЗ.ДнейПросрочки = Неопределено; // Или 0, или другое значение
    КонецЕсли;
КонецЦикла;

// Теперь ТаблицаРезультат содержит колонку ДнейПросрочки с корректными значениями

Преимущества: Этот метод очень надежен, так как полностью исключает влияние функции РАЗНОСТЬДАТ на логику запроса и его итогов. Он позволяет иметь полный контроль над расчетом. Недостатки: Может быть менее производительным для очень больших объемов данных, так как требует дополнительного цикла по всей выборке.

Дополнительные рекомендации и важные моменты

Проверка параметров РАЗНОСТЬДАТ: Всегда убеждайтесь, что в функцию РАЗНОСТЬДАТ передаются только корректные значения даты. Ошибки типа "Неверные параметры РАЗНОСТЬДАТ" могут возникать, если один из параметров не является действительным значением даты.
Понимание РАЗНОСТЬДАТ: Помните, что функция РАЗНОСТЬДАТ отбрасывает все более мелкие единицы измерения, чем та, которая указана в третьем параметре. Например, если указан тип "ДЕНЬ", то часы, минуты и секунды в обеих датах игнорируются, и даты фактически приводятся к началу дня для расчета разницы. Это важно учитывать, если вам требуется более точный расчет интервала времени.
Тестирование на различных СУБД: Если вы работаете в клиент-серверном варианте, всегда полезно протестировать запросы на различных СУБД (например, PostgreSQL, MS SQL Server), если ваша конфигурация поддерживает такую гибкость, чтобы выявить специфические особенности поведения.

Мы видим, что проблема с некорректными итогами при добавлении функции РАЗНОСТЬДАТ в запрос 1С является многогранной и может быть вызвана различными факторами, от особенностей "дат начала времен" до специфики работы СУБД. Однако, как мы убедились, существуют эффективные методы решения этой задачи.

Выбирайте тот подход, который наилучшим образом соответствует вашей ситуации: будь то условный расчет РАЗНОСТЬДАТ для исключения проблемных дат, использование пакетных запросов для разделения логики или постобработка данных в коде 1С. Главное – понять корневую причину и применить адекватное решение.

← К списку