Как получить начальные остатки на начало каждого месяца в запросе 1С при отсутствии движений?

← На главную

При построении помесячных отчетов по складским остаткам разработчики часто сталкиваются с ситуацией, когда по номенклатуре в определенные месяцы не было никаких приходов и расходов. Если запросить данные напрямую из виртуальной таблицы РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты с периодичностью Месяц, строки за такие «пустые» периоды просто не попадут в результат. В итоге остаток на начало месяца пропадает, а непрерывная хронология нарушается.

Разберем причины такого поведения платформы 1С и подробно рассмотрим рабочие способы решения этой задачи как в тексте запроса, так и средствами Системы компоновки данных (СКД).

Почему виртуальная таблица не возвращает остатки за месяцы без движений?

Проанализируем механику работы виртуальной таблицы ОстаткиИОбороты. Платформа 1С оптимизирует запросы к базе данных и формирует срезы только для тех интервалов времени, внутри которых были зафиксированы движения по указанной комбинации измерений (например, Номенклатура, ХарактеристикаНоменклатуры, Склад). Если за весь месяц по товару не проводился ни один документ, запись за этот месяц отсутствует в физических таблицах итогов и движений за период, а значит, виртуальная таблица не будет генерировать пустую строку с перенесенным остатком.

Попытка решить задачу простым условием ВЫБОР КОГДА внутри одного месяца не дает результата, так как самой строки с нужным периодом нет в выборке. Чтобы получить остаток на начало каждого месяца, необходимо сформировать непрерывную сетку периодов и сопоставить ей последний актуальный остаток.

Способ 1. Формирование сетки периодов через календарь в запросе

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

Разберем по шагам построение запроса:

Формирование таблицы месяцев (ПериодыМ): выбираем уникальные начала месяцев за интересующий интервал из производственного календаря (РегистрСведений.РегламентированныйПроизводственныйКалендарь).
Получение остатков и оборотов (ОстаткиПоМесяцам): извлекаем начальные и конечные остатки с периодичностью Месяц по нужным отборам.
Связывание периодов: выполняем левое соединение сетки месяцев с таблицей остатков по условию ПериодыМ.ПериодМесяц >= ОстаткиПоМесяцам.ПериодМесяц и с помощью функции МАКСИМУМ находим ближайший предшествующий период с движениями.
Вычисление остатка: если месяц совпадает с месяцем движения, берем начальный остаток этого месяца; если в текущем месяце движений не было, берем конечный остаток предыдущего активного периода.

Посмотрим на готовый пример запроса:


ВЫБРАТЬ РАЗРЕШЕННЫЕ
    НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря, МЕСЯЦ) КАК ПериодМесяц
ПОМЕСТИТЬ ПериодыМ
ИЗ
    РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК РегламентированныйПроизводственныйКалендарь
ГДЕ
    РегламентированныйПроизводственныйКалендарь.ДатаКалендаря МЕЖДУ &НачалоПериода И &КонецПериода
СГРУППИРОВАТЬ ПО
    НАЧАЛОПЕРИОДА(РегламентированныйПроизводственныйКалендарь.ДатаКалендаря, МЕСЯЦ)
;

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ОпределениеПериода.ПериодМесяц КАК ПериодМесяц,
    ОпределениеПериода.Номенклатура КАК Номенклатура,
    ОпределениеПериода.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    ОпределениеПериода.ПериодМесяцДляСвязи КАК ПериодМесяцДляСвязи,
    ВЫБОР
        КОГДА ОпределениеПериода.ПериодМесяц = ОпределениеПериода.ПериодМесяцДляСвязи
            ТОГДА ОстаткиПоМесяцам.НачальныйОстатокПоМесяцам
        ИНАЧЕ ОстаткиПоМесяцам.КонечныйОстатокПоМесяцам
    КОНЕЦ КАК Остаток
ИЗ
    (ВЫБРАТЬ
        ПериодыМ.ПериодМесяц КАК ПериодМесяц,
        ОстаткиПоМесяцам.Номенклатура КАК Номенклатура,
        ОстаткиПоМесяцам.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
        МАКСИМУМ(ОстаткиПоМесяцамПериоды.ПериодМесяц) КАК ПериодМесяцДляСвязи
    ИЗ
        ПериодыМ КАК ПериодыМ
            ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиПоМесяцам КАК ОстаткиПоМесяцамПериоды
                ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиПоМесяцам КАК ОстаткиПоМесяцам
                ПО ОстаткиПоМесяцамПериоды.Номенклатура = ОстаткиПоМесяцам.Номенклатура
                    И ОстаткиПоМесяцамПериоды.ХарактеристикаНоменклатуры = ОстаткиПоМесяцам.ХарактеристикаНоменклатуры
            ПО ПериодыМ.ПериодМесяц >= ОстаткиПоМесяцамПериоды.ПериодМесяц
    СГРУППИРОВАТЬ ПО
        ПериодыМ.ПериодМесяц,
        ОстаткиПоМесяцам.Номенклатура,
        ОстаткиПоМесяцам.ХарактеристикаНоменклатуры) КАК ОпределениеПериода
        ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиПоМесяцам КАК ОстаткиПоМесяцам
        ПО ОпределениеПериода.Номенклатура = ОстаткиПоМесяцам.Номенклатура
            И ОпределениеПериода.ХарактеристикаНоменклатуры = ОстаткиПоМесяцам.ХарактеристикаНоменклатуры
            И ОпределениеПериода.ПериодМесяцДляСвязи = ОстаткиПоМесяцам.ПериодМесяц

Способ 2. Штатное дополнение периодов в СКД

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

Разберем настройку по шагам:

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

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

Способ 3. Расчет остатков нарастающим итогом

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

В отдельную временную таблицу получаем срез остатков на общую дату &НачалоПериода через таблицу ТоварыНаСкладах.Остатки.
Во вторую таблицу выбираем помесячные обороты через ТоварыНаСкладах.Обороты.
Формируем полную сетку месяцев и рассчитываем начальный остаток каждого месяца как: ОстатокНаСтарт + СУММА(ОборотыЗаПредыдущиеМесяцы).
В версиях платформы 1С:Предприятие 8.3.20 и новее для расчета нарастающего итога можно применить оконную функцию СУММА(КоличествоОборот) OVER (PARTITION BY Номенклатура ORDER BY ПериодМесяц ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), что значительно ускоряет выполнение запроса на больших базах.

Важные замечания и производительность

При использовании запросов с заполнением периодов обратим внимание на следующие технические особенности:

Заполнение календаря: при получении сетки месяцев из РегистрСведений.РегламентированныйПроизводственныйКалендарь убедимся, что календарь в базе заполнен на все проверяемые годы. Если нужного года в календаре нет, запрос вернет пустой результат. В качестве альтернативы таблицу дат можно передать в запрос через параметр (массив или таблицу значений).

Нагрузка на СУБД: соединения вида ПериодыМ.ПериодМесяц >= Движения.ПериодМесяц выполняются на уровне СУБД как неэквивалентные (вложенные циклы). При большом объеме номенклатуры и широком диапазоне дат такой запрос может создавать высокую нагрузку, поэтому для тяжелых отчетов предпочтительнее использовать штатные механизмы СКД или оконные функции.