При работе с электронными счетами-фактурами (ЭСФ) в системе 1С программисты часто сталкиваются с ситуацией, когда одному документу-основанию (например, РеализацияТоваровУслуг) соответствует несколько записей в регистре сведений АктуальныеЭСФ. Это происходит, если по одной реализации выставлялось несколько счетов-фактур, создавались исправления или дополнительные документы. В результате обычное соединение таблиц в запросе приводит к «размножению» строк: одна реализация выводится в списке столько раз, сколько записей найдено в регистре.
В данной статье мы разберем, как правильно построить запрос, чтобы получить только один, самый актуальный документ ЭСФ для каждой реализации, и проанализируем типичные ошибки, возникающие при использовании агрегатных функций и временных таблиц.
Рассмотрим ситуацию, с которой столкнулся автор темы. Изначально запрос выглядел следующим образом:
ВЫБРАТЬ РАЗЛИЧНЫЕ
РеализацияТоваровУслуг.Ссылка КАК Ссылка,
АктуальныеЭСФ.ЭСФ.Дата КАК ЭСФДата,
АктуальныеЭСФ.СчетФактура.Дата КАК СчетФактураДата
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.АктуальныеЭСФ КАК АктуальныеЭСФ
ПО РеализацияТоваровУслуг.Ссылка = АктуальныеЭСФ.СчетФактура.ДокументОснование
Проблема этого кода заключается в том, что ЛЕВОЕ СОЕДИНЕНИЕ находит все записи в регистре АктуальныеЭСФ, у которых поле ДокументОснование совпадает со ссылкой реализации. Если в базе по одной реализации создано две ЭСФ (например, от разных дат), запрос честно вернет две строки. Использование ключевого слова РАЗЛИЧНЫЕ здесь не помогает, так как даты в этих строках разные, и с точки зрения СУБД эти строки не являются идентичными.
Для решения проблемы часто пытаются использовать группировку и функцию МАКСИМУМ. Однако здесь кроется ловушка. Если мы просто добавим МАКСИМУМ(Дата) и сгруппируем по ссылке документа, мы получим актуальную дату. Но как только нам потребуется вытянуть саму ссылку на ЭСФ или другие связанные данные, нам придется либо включать их в группировку (что снова вернет дубли), либо делать соединение.
Рассмотрим неудачную попытку исправления из сообщения №2:
ВЫБРАТЬ
ЭСФ.ДокументОснование КАК ДокументОснование,
МАКСИМУМ(ЭСФ.СчетФактураДата) КАК СчетФактураДата
ПОМЕСТИТЬ ПервыеЭСФ
ИЗ
ЭСФ КАК ЭСФ
СГРУППИРОВАТЬ ПО
ЭСФ.ДокументОснование
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ЭСФ.ЭСФДата КАК ЭСФДата,
ЭСФ.СчетФактураДата КАК СчетФактураДата,
ПервыеЭСФ.ДокументОснование КАК ДокументОснование
ИЗ
ЭСФ КАК ЭСФ
ЛЕВОЕ СОЕДИНЕНИЕ ПервыеЭСФ КАК ПервыеЭСФ
ПО ЭСФ.СчетФактураДата = ПервыеЭСФ.СчетФактураДата
В чем здесь ошибка? Проанализируем условие связи: ПО ЭСФ.СчетФактураДата = ПервыеЭСФ.СчетФактураДата. Здесь отсутствует связь по объекту-основанию. Если в системе есть два разных документа реализации, счета-фактуры которых были выписаны в одну и ту же секунду, произойдет «перекрестное» соединение, и данные перемешаются, породив еще больше дублей.
Чтобы гарантированно получить одну уникальную запись для каждого документа-основания, необходимо придерживаться строгого алгоритма. Разберем его по шагам.
Шаг 1. Подготовка среза последних данных.
Сначала мы должны определить критерий «актуальности». Обычно это максимальная дата ЭСФ или максимальный момент времени. Создаем временную таблицу, в которой будет храниться только пара: ДокументОснование и его МаксимальнаяДата.
ВЫБРАТЬ
АктуальныеЭСФ.СчетФактура.ДокументОснование КАК ДокументОснование,
МАКСИМУМ(АктуальныеЭСФ.ЭСФ.Дата) КАК МаксДатаЭСФ
ПОМЕСТИТЬ ТаблицаМаксимальныхДат
ИЗ
РегистрСведений.АктуальныеЭСФ КАК АктуальныеЭСФ
ГДЕ
АктуальныеЭСФ.СчетФактура.ДокументОснование ССЫЛКА Документ.РеализацияТоваровУслуг
СГРУППИРОВАТЬ ПО
АктуальныеЭСФ.СчетФактура.ДокументОснование
ИНДЕКСИРОВАТЬ ПО
ДокументОснование,
МаксДатаЭСФ
;
Шаг 2. Соединение по составному ключу.
Теперь мы выбираем данные из основного регистра, соединяя его с нашей временной таблицей обязательно по двум полям: ссылке на документ и дате. Это исключит ситуацию, когда дата совпадает у разных документов.
ВЫБРАТЬ
АктуальныеЭСФ.СчетФактура.ДокументОснование КАК ДокументОснование,
АктуальныеЭСФ.ЭСФ КАК ЭСФ,
АктуальныеЭСФ.ЭСФ.Дата КАК ЭСФДата
ПОМЕСТИТЬ ИтоговыеДанныеЭСФ
ИЗ
РегистрСведений.АктуальныеЭСФ КАК АктуальныеЭСФ
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ТаблицаМаксимальныхДат КАК ТМД
ПО АктуальныеЭСФ.СчетФактура.ДокументОснование = ТМД.ДокументОснование
И АктуальныеЭСФ.ЭСФ.Дата = ТМД.МаксДатаЭСФ
Шаг 3. Финальная выборка.
На этом этапе мы уже имеем чистые данные без дублей (при условии, что по одному документу не создано две ЭСФ в одну и ту же секунду). Теперь мы можем соединить это с основной таблицей реализаций.
Проанализируем дополнительные нюансы, которые могут повлиять на результат запроса:
МАКСИМУМ(Ссылка). Ссылка в 1С всегда уникальна, что позволит выбрать строго одну запись из нескольких «одновременных».Актуальные ЭСФ могут находиться документы в разных состояниях: «Аннулирован», «Отозван», «Ошибка». Чтобы ваш запрос не подхватил аннулированный документ как актуальный только потому, что он был создан позже всех, обязательно добавляйте условие: ГДЕ АктуальныеЭСФ.Состояние <> ЗНАЧЕНИЕ(Перечисление.СостоянияЭСФ.Аннулирован).ПОМЕСТИТЬ) важно индексировать поля, по которым будет происходить последующее соединение. В нашем примере это ДокументОснование. Это критически важно для производительности на больших базах.Актуальные ЭСФ является периодическим, вместо ручного поиска максимума эффективнее использовать виртуальную таблицу СрезПоследних. Она работает на уровне СУБД быстрее и возвращает только актуальные записи на указанную дату.Таким образом, для решения задачи автора сообщения №9, ключевым моментом стало использование внутреннего соединения вместо левого и правильная связка по ключевым полям в итоговом пакете запроса. Это позволило отсечь лишние исторические записи и оставить только те, которые соответствуют критериям актуальности.