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

← На главную

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

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

Почему нельзя использовать агрегатную функцию МАКСИМУМ к ссылке документа

В языке запросов 1С функция МАКСИМУМ(), примененная к ссылочному полю (Ссылка), не ориентируется на хронологию или дату документа. Сравнение ссылок в СУБД происходит по их внутреннему бинарному представлению (GUID). Если дата документа была изменена или документы вводились задним числом, ссылка останется неизменной и не будет отражать хронологический порядок.

Единственный верный признак хронологии — реквизит Дата документа (или специализированные реквизиты периода, такие как КонецПериода в актах сверки).

Классический способ: пакетный запрос с временной таблицей

Разберем классический и наиболее совместимый алгоритм, работающий на любых версиях платформы 1С:Предприятие 8. Алгоритм состоит из четырех последовательных шагов:

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

Посмотрим на пример практической реализации данного запроса:


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

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

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

Проанализируем ситуацию, когда по одной и той же паре «Контрагент–Договор» оформлено несколько документов с одинаковой датой и временем вплоть до секунды. В этом случае соединение по условию Дата = МаксимальныеДаты.Дата вернет несколько записей, и итоговая строка отчета с остатком продублируется.

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


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

Современное решение: аналитические оконные функции (1С 8.3.20+)

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

Используем оконную функцию ROW_NUMBER() с разбиением по аналитике и сортировкой по убыванию даты:


ВЫБРАТЬ
    Акты.Контрагент КАК Контрагент,
    Акты.ДоговорКонтрагента КАК ДоговорКонтрагента,
    Акты.Ссылка КАК Ссылка,
    Акты.СверкаСогласована КАК СверкаСогласована,
    НОМЕР_СТРОКИ() OVER (
        PARTITION BY Акты.Контрагент, Акты.ДоговорКонтрагента 
        ORDER BY Акты.Дата УБЫВ, Акты.Ссылка УБЫВ
    ) КАК НомерПоПорядку
ПОМЕСТИТЬ ВТ_ПронумерованныеАкты
ИЗ
    Документ.АктСверкиВзаиморасчетов КАК Акты
ГДЕ
    НЕ Акты.ПометкаУдаления
;

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

Реализация средствами СКД без усложнения текста запроса

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

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

ВычислитьВыражение("Ссылка", "", "Группировка", "Первая", "Дата Убыв")

Такой подход переносит логику вычисления актуального документа на процессор компоновки данных и сохраняет текст запроса простым и легко читаемым.