Как получить остатки на каждый день в 1С?

← На главную

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

Почему простое соединение календаря с таблицей «ОстаткиИОбороты» дает неверный результат

Проанализируем типовую попытку решения: сформировать таблицу дат (календарь) и соединить ее левым соединением с виртуальной таблицей ОстаткиИОбороты регистра накопления с периодичностью ДЕНЬ.

При таком подходе на дату начала и окончания интервала остатки сходятся, а в середине интервала появляются нулевые значения или пропуски. Выясним причину такого поведения платформы:

Особенность виртуальной таблицы: при указании периодичности ДЕНЬ виртуальная таблица ОстаткиИОбороты возвращает записи только за те дни, в которых были фактические движения по конкретной комбинации измерений (а также точки границ интервала при наличии ненулевого остатка). Если по товару три дня не было продаж и поступлений, платформа не генерирует строки за эти дни.
Потеря аналитики при соединении: выполняя простое левое соединение таблицы дат с таблицей движений по условию ДНИ.Период = Остатки.Период, мы не связываем дни с аналитическими разрезами (складами, товарами). В дни затишья мы получаем в полях товара и склада значение NULL, а остаток при замене ЕСТЬNULL обращается в ноль, хотя физически товар на складе лежит.

Причина возникновения «Ошибки SDBL»

При попытке рассчитать дополнительный реквизит (например, литраж) прямо в запросе к виртуальной таблице разработчики часто пишут конструкции вида:


Движения.КоличествоКонечныйОстаток * Движения.Номенклатура.ЕдиницаХраненияОстатков.Литраж

При выполнении такого запроса за длительный период СУБД возвращает системный сбой: Ошибка SDBL. Разберем механику этой ошибки:

Разыменование ссылочных полей через несколько точек (Номенклатура.ЕдиницаХраненияОстатков.Литраж) заставляет транслятор 1С генерировать множество вложенных неявных соединений LEFT JOIN. Когда это накладывается на сложную виртуальную таблицу остатков и оборотов, результирующий SQL-запрос превышает пределы сложности парсера платформы или ограничения СУБД. Чтобы избежать сбоя, необходимо выбирать из регистра только прямые ссылки, а справочники с их реквизитами присоединять явно через отдельное левое соединение на финальном этапе выборки.

Решение 1. Использование встроенных механизмов СКД (Рекомендуемый способ)

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

Разберем по шагам правильную настройку схемы компоновки данных:

Упрощаем запрос набора данных: выбираем данные напрямую из таблицы ОстаткиИОбороты без самодельных календарей:


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

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

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

Решение 2. Расчет остатка на каждый день в тексте запроса

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

Рассмотрим структуру запроса:

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

Посмотрим на пример реализации запроса:


ВЫБРАТЬ
    ДНИ.Период КАК Период,
    Аналитика.Склад КАК Склад,
    Аналитика.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Сетка
ИЗ
    ДНИ КАК ДНИ
        ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Аналитика КАК Аналитика
        ПО (ИСТИНА)
;

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

Важное ограничение производительности: соединение по неэквивалентному условию Обороты.Период <= Сетка.Период создает квадратичную сложность ($O(N^2)$). На больших объемах данных и широких временных диапазонах этот запрос будет выполняться медленно. Если версия платформы 1С 8.3.20 и выше, оптимальнее использовать оконные функции СУБД (конструкция OVER (PARTITION BY ... ORDER BY ...)), которые рассчитывают нарастающий итог без тяжелых соединений по неравенству.