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

← На главную

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

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

Почему обычный СрезПоследних не работает внутри соединения

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


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&КонецПериода, ) КАК Цены
ПО Продажи.Номенклатура = Цены.Номенклатура

Проблема заключается в том, что 1С рассчитывает виртуальную таблицу СрезПоследних один раз на дату, указанную в параметре (в данном случае &КонецПериода). Если цена на товар менялась в течение месяца, то для всех документов продажи за этот месяц запрос подтянет одну и ту же последнюю цену, что фактически искажает данные отчета. Если же на дату &КонецПериода цены нет, запрос вернет NULL, даже если цена была установлена ранее.

Проанализируем ситуацию: нам нужно для каждой строки продажи найти в регистре сведений ЦеныНоменклатуры запись, у которой период меньше или равен дате документа, и при этом эта запись должна быть самой «свежей» из всех предшествующих. Рассмотрим, как реализовать это правильно.

Метод «Максимального периода»: пошаговый алгоритм

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

Сбор данных о продажах. Сначала мы выбираем все необходимые данные из регистра продаж и помещаем их во временную таблицу. Нам важно получить уникальные пары «Номенклатура — Дата документа».
Поиск дат установки цен. Мы соединяем наши продажи с реальной (физической) таблицей регистра сведений ЦеныНоменклатуры. Условие соединения: период записи регистра должен быть меньше или равен дате документа продажи. С помощью группировки по каждой строке продажи мы находим МАКСИМУМ(Период) из регистра цен.
Получение самих значений цен. Имея точную дату последней установки цены для каждой продажи, мы снова соединяемся с физической таблицей регистра цен, но теперь уже по точному совпадению периода и номенклатуры.

Разберем этот алгоритм на примере кода.

Пример реализации запроса через временные таблицы

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


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

////////////////////////////////////////////////////////////////////////////////
// ЭТАП 2: Находим дату последней установки цены перед продажей
ВЫБРАТЬ
    Продажи.Номенклатура КАК Номенклатура,
    Продажи.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
    Продажи.ДокументПродажи КАК ДокументПродажи,
    МАКСИМУМ(Цены.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_ДатыЦен
ИЗ
    ВТ_Продажи КАК Продажи
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК Цены
        ПО Продажи.Номенклатура = Цены.Номенклатура
            И Продажи.ХарактеристикаНоменклатуры = Цены.ХарактеристикаНоменклатуры
            И Продажи.ДатаДокумента >= Цены.Период
ГДЕ
    Цены.ТипЦен = &ВыбранныйТипЦен
СГРУППИРОВАТЬ ПО
    Продажи.Номенклатура,
    Продажи.ХарактеристикаНоменклатуры,
    Продажи.ДокументПродажи;

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

Анализ производительности и типичные ошибки

Проанализируем несколько важных моментов, которые помогут вашему запросу работать быстрее и стабильнее:

Использование функции ЕСТЬNULL. Всегда оборачивайте поля цены из регистра сведений в функцию ЕСТЬNULL(Цена, 0). Если на какой-то товар цена никогда не устанавливалась, результат соединения будет NULL, что приведет к ошибкам в арифметических вычислениях (например, при расчете налога или прибыли).
Обращение через точку (Разыменование). Избегайте конструкций вида Продажи.Организация.Наименование ПОДОБНО "%ИП%". В языке SQL 1С каждое обращение через точку к реквизиту ссылочного типа порождает неявное соединение (LEFT JOIN) с соответствующей таблицей. Если в отчете тысячи строк, это создает колоссальную нагрузку. Правильнее передавать ссылку на конкретную организацию в параметрах запроса: ГДЕ Организация = &ОрганизацияСсылка.
Оператор ПОДОБНО. Использование ПОДОБНО для сравнения ссылочных объектов по их строковому представлению — крайне плохая практика. Сравнение строк в базе данных происходит значительно медленнее, чем сравнение уникальных идентификаторов (ссылок).
Группировка и дубли. Если вы заметили, что строки в отчете начали «утраиваться», проверьте измерения регистра цен. Часто забывают наложить фильтр по ВидуЦен или ХарактеристикеНоменклатуры, из-за чего на одну дату документа система находит несколько цен и создает дублирующиеся строки.

Альтернатива: Связи в Системе компоновки данных (СКД)

Если вы разрабатываете отчет на базе СКД, то вышеописанный сложный запрос можно заменить более простым механизмом Связи наборов данных. Рассмотрим этот вариант подробнее:

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

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