Как в запросе 1С получить последнюю цену товара, если цены хранятся в разных валютах?

← На главную

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

Рассмотрим, как правильно составить запрос, чтобы для каждой номенклатуры получить только одну, самую последнюю по дате, цену, независимо от валюты, и использовать ее для дальнейших расчетов.

Анализ проблемы: почему простой запрос не работает

Предположим, у нас есть регистр накопления ТоварыНаСкладах для получения остатков и периодический регистр сведений ЦеныПоступлений, где хранятся цены. Изначальная попытка решить задачу "в лоб" может выглядеть так:


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

В чем ошибка этого подхода? Вложенный запрос выбирает все цены для номенклатуры, которые были установлены до даты отчета. Если для товара "Стол" были поступления за 100 USD и 9000 RUB, то после соединения мы получим две строки для этого товара, что неверно. Нам нужна только одна строка с самой последней ценой.

Правильное решение: многошаговый запрос с временными таблицами

Чтобы гарантированно получить одну, самую последнюю запись о цене, используем подход с временными таблицами. Логика разбивается на три простых шага:

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

Разберем каждый шаг подробно.

Шаг 1: Находим дату последнего поступления

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


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

После выполнения этого пакета запроса у нас будет временная таблица ПоследниеПоступления с двумя колонками: Номенклатура и ДатаПоследнегоПоступления. В ней для каждой номенклатуры будет только одна строка.

Шаг 2: Получаем полные данные о последних ценах

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


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

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

Шаг 3: Соединяем остатки с последними ценами

Остался финальный шаг — объединить данные об остатках с полученными ценами. Мы берем остатки из виртуальной таблицы РегистрНакопления.ТоварыНаСкладах.Остатки и присоединяем к ним нашу таблицу ПоследниеЦены с помощью ЛЕВОГО СОЕДИНЕНИЯ.

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

Итоговый рабочий запрос

Объединив все три шага, мы получаем полный и корректный запрос для решения нашей задачи:


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

Почему не использовать виртуальную таблицу `СрезПоследних`?

Механизм СрезПоследних — это стандартный и очень быстрый способ получения актуальных данных из периодических регистров. Однако в нашей задаче он не подходит. СрезПоследних вернет последние записи в разрезе всех измерений регистра. Если Валюта является измерением, то для товара, у которого есть цены в USD и EUR, срез вернет две строки: последнюю цену в USD и последнюю цену в EUR.

Нам же требовалось получить одну, хронологически самую последнюю цену, независимо от валюты. Именно эту задачу и решает метод с поиском максимального периода.

Что еще стоит учесть?

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

← На главную