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

← На главную

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

Первоначальный вариант: самосоединение таблиц

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

Проанализируем этот метод. Вот пример запроса, демонстрирующий эту логику:


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

Ключевая идея здесь в условии ГДЕ ТП2.Ссылка ЕСТЬ NULL. Мы пытаемся для каждой строки закупки ТП найти более "свежую" закупку ТП2. Если такая находится, значит ТП — не последняя, и мы ее отбрасываем. Если же для ТП не нашлось более новой записи (ТП2.Ссылка ЕСТЬ NULL), значит, это и есть последняя на нужный момент времени.

Хотя этот метод рабочий, у него есть недостатки:

Сложность. Запрос трудно читать и понимать, особенно без комментариев.
Производительность. Множественные соединения, особенно с вложенными запросами без должной оптимизации, могут медленно работать на больших объемах данных.
Стиль. Использование коротких, неочевидных псевдонимов (ТО, ТП, ТП2) считается плохой практикой и затрудняет сопровождение кода.

К счастью, существуют более простые и стандартные способы решения этой задачи.

Решение 1: Использование регистра сведений "Цены номенклатуры"

В большинстве типовых конфигураций 1С для хранения цен используется периодический регистр сведений, например, РегистрСведений.ЦеныНоменклатуры. Это наиболее правильный и производительный способ.

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

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

Пример запроса для получения последней цены на каждую дату продажи:


ВЫБРАТЬ
    Продажи.Номенклатура,
    Продажи.Ссылка КАК ДокументПродажи,
    Продажи.Количество,
    ЕСТЬNULL(ЦеныЗакупки.Цена, 0) КАК ЦенаЗакупочная
ИЗ
    Документ.РеализацияТоваровУслуг.Товары КАК Продажи
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(
                , // Дата среза; оставляем пустой, чтобы получить все записи
                ТипЦен = &ТипЦенЗакупочная // Отбор по типу цен
            ) КАК ЦеныЗакупки
        ПО Продажи.Номенклатура = ЦеныЗакупки.Номенклатура
            И Продажи.Ссылка.Дата >= ЦеныЗакупки.Период
// Это условие соединения некорректно для общего случая, но иллюстрирует идею.
// Правильный способ — использовать более сложные конструкции или временные таблицы.

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

Решение 2: Коррелированный подзапрос с "ВЫБРАТЬ ПЕРВЫЕ 1"

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

Логика метода:

Для каждой строки документа продажи мы выполняем небольшой вложенный запрос (подзапрос). Этот подзапрос делает следующее:

Находит все строки в документах поступления для той же номенклатуры.
Отбирает только те поступления, дата которых меньше или равна дате нашей продажи.
Сортирует найденные поступления по дате в обратном порядке (от новых к старым).
Берет самую первую запись из отсортированного списка с помощью конструкции ВЫБРАТЬ ПЕРВЫЕ 1.

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

Шаг 1. Базовый запрос

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


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

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

Шаг 2. Улучшения для надежности

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

1. Проверка на проведенные документы.

В расчетах должны участвовать только проведенные документы, так как непроведенные являются черновиками и не отражаются в учете. Добавим в подзапрос условие ЗакупкиПоследние.Ссылка.Проведен = ИСТИНА.

2. Обработка документов с одинаковой датой.

Два разных документа поступления могут быть созданы в одну и ту же секунду. Чтобы результат запроса был всегда одинаковым (детерминированным), нужно добавить второй уровень сортировки. Для этого идеально подходит сама ссылка на документ, так как она уникальна. Упорядочим не только по дате, но и по ссылке: УПОРЯДОЧИТЬ ПО ...Дата УБЫВ, ...Ссылка УБЫВ.

3. Обработка нескольких строк в одном документе.

Если в одном документе поступления есть несколько строк с одной и той же номенклатурой (возможно, с разной ценой), наш запрос может выбрать не ту строку или размножить результаты. Чтобы выбрать самую последнюю строку внутри документа, можно добавить в сортировку поле НомерСтроки: УПОРЯДОЧИТЬ ПО ...Дата УБЫВ, ...Ссылка УБЫВ, ...НомерСтроки УБЫВ.

Итоговый, надежный вариант запроса:

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


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

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