Как разделить запрос к таблице ОстаткиИОбороты на остатки и обороты в 1С?

← На главную

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

Проанализируем проблему таблицы ОстаткиИОбороты

Многие начинающие программисты пытаются использовать конструкцию вида:


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

Выясним причину, почему это не работает корректно. Поле Регистратор в таблице ОстаткиИОбороты существует только тогда, когда задана периодичность "Регистратор" или "Запись". Если мы накладываем условие на тип документа (в секции ГДЕ или в параметрах виртуальной таблицы), система отфильтрует весь сгенерированный набор данных. В итоге из выборки исчезнут документы прихода, перемещения или списания. А вместе с ними исказятся или полностью пропадут начальные и конечные остатки, так как система будет пытаться считать "остатки" только в рамках разрешенных документов продаж. В чистой таблице остатков регистратора не существует в принципе, так как остаток — это статичный срез на момент времени.

Почему не подходит внутреннее соединение (INNER JOIN)?

Посмотрим на пример попытки решить задачу через разделение запроса на таблицы Остатки и Обороты с использованием внутреннего соединения. Разработчик пытается связать их по номенклатуре и складу:


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

В этом случае возникает проблема пропадающих записей. Это естественное поведение внутреннего соединения. Оно оставляет в результате только те строки, которые имеют совпадения в обеих таблицах. Если товар лежит на складе (есть в остатках), но по нему не было нужных документов в выбранном периоде (нет в оборотах), внутреннее соединение полностью исключит этот товар из итоговой таблицы. Аналогично, если товар был продан "в минус" при нулевом начальном остатке, его тоже не будет в результате.

Решение 1: Использование полного соединения (FULL JOIN)

Чтобы остатки не терялись при отсутствии оборотов (и наоборот), таблицы нужно связывать через ПОЛНОЕ СОЕДИНЕНИЕ. Разберем этот вариант.

При использовании полного соединения возникает нюанс: если запись есть только в одной из таблиц, поля другой таблицы будут иметь значение NULL. Поэтому в секции выборки необходимо "склеивать" измерения с помощью функции ЕСТЬNULL.


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

Здесь важно обратить внимание на условие Обороты.Регистратор ЕСТЬ NULL, которое позволяет не потерять остатки по товарам, по которым не было оборотов.

Решение 2: Использование ОБЪЕДИНИТЬ ВСЕ (Рекомендуемый метод)

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

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

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


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

    ОБЪЕДИНИТЬ ВСЕ

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

СГРУППИРОВАТЬ ПО
    ВложенныйЗапрос.Номенклатура

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