При построении отчетов или обработок по регистрам бухгалтерии и регистрам накопления разработчики нередко сталкиваются с ситуацией, когда по одному и тому же документу возвращается сразу несколько строк с остатками на разные даты. Рассмотрим типичный пример: при обращении к виртуальной таблице формируется выборка, где один документ фигурирует несколько раз с остатками на 15, 21 и 25 число месяца. Наша задача — оставить в результирующем наборе данных исключительно одну запись с самым актуальным остатком на наиболее позднюю дату.
Выясним причину такого поведения виртуальной таблицы, разберем классическое решение через временные таблицы и группировку, а также изучим более современные и производительные альтернативы.
Проанализируем ситуацию, из-за которой возникает дублирование строк по одному документу. Главная причина кроется в настройках виртуальной таблицы ОстаткиИОбороты. Если в параметрах этой таблицы указана периодичность (например, Период, День, Месяц, Регистратор или Запись), платформа рассчитывает остаток на каждую границу указанного периода, в котором происходили движения.
Если для одного документа-субконто или регистратора было несколько движений в разные дни, таблица честно сформирует срез остатка на каждый из этих моментов времени. В результате в выборку попадает история изменения остатков, тогда как нам требуется лишь конечное сальдо.
Разберем по шагам классический способ решения, предложенный на форуме. Он работает на всех версиях платформы 1С:Предприятие 8 и строится на пакетном запросе с промежуточными временными таблицами.
МАКСИМУМ(Период).Посмотрим на пример реализации такого пакетного запроса:
ВЫБРАТЬ
ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Документ,
ХозрасчетныйОстаткиИОбороты.Период КАК Период,
ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстаток КАК Остаток
ПОМЕСТИТЬ ВТ_ВсеОстатки
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Запись, , Счет = &Счет, , ) КАК ХозрасчетныйОстаткиИОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ВсеОстатки.Документ КАК Документ,
МАКСИМУМ(ВТ_ВсеОстатки.Период) КАК МаксимальнаяДата
ПОМЕСТИТЬ ВТ_МаксимальныеДаты
ИЗ
ВТ_ВсеОстатки КАК ВТ_ВсеОстатки
СГРУППИРОВАТЬ ПО
ВТ_ВсеОстатки.Документ
ИНДЕКСИРОВАТЬ ПО
Документ,
МаксимальнаяДата
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_ВсеОстатки.Документ КАК Документ,
ВТ_ВсеОстатки.Период КАК Период,
ВТ_ВсеОстатки.Остаток КАК Остаток
ИЗ
ВТ_МаксимальныеДаты КАК ВТ_МаксимальныеДаты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ВсеОстатки КАК ВТ_ВсеОстатки
ПО ВТ_МаксимальныеДаты.Документ = ВТ_ВсеОстатки.Документ
И ВТ_МаксимальныеДаты.МаксимальнаяДата = ВТ_ВсеОстатки.Период
Важное замечание: если внутри одной секунды по документу было проведено два движения, соединение только по дате вернет обе записи. Чтобы устранить эту коллизию, группировку и соединение следует расширять дополнительными полями упорядочивания, такими как Регистратор и НомерСтроки (или использовать МАКСИМУМ(Регистратор)).
Рассмотрим современный способ, доступный в версиях платформы 1С 8.3.20 и выше. Благодаря поддержке аналитических оконных функций отпадает необходимость создавать несколько временных таблиц и выполнять повторные соединения, создающие нагрузку на СУБД.
Мы можем пронумеровать записи внутри группы каждого документа в порядке убывания даты с помощью функции НОМЕР_СТРОКИ() и конструкции OVER (...), а затем во внешнем запросе отобрать только строки с первым номером:
ВЫБРАТЬ
ВложенныйЗапрос.Документ КАК Документ,
ВложенныйЗапрос.Период КАК Период,
ВложенныйЗапрос.Остаток КАК Остаток
ИЗ
(ВЫБРАТЬ
ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Документ,
ХозрасчетныйОстаткиИОбороты.Период КАК Период,
ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстаток КАК Остаток,
НОМЕР_СТРОКИ() OVER (
РАЗДЕЛИТЬ ПО ХозрасчетныйОстаткиИОбороты.Субконто1
УПОРЯДОЧИТЬ ПО ХозрасчетныйОстаткиИОбороты.Период УБЫВ
) КАК НомерЗаписи
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Запись, , Счет = &Счет, , ) КАК ХозрасчетныйОстаткиИОбороты) КАК ВложенныйЗапрос
ГДЕ
ВложенныйЗапрос.НомерЗаписи = 1
Этот запрос отрабатывает за один проход по данным, что существенно экономит ресурсы сервера и упрощает чтение исходного кода.
Проанализируем саму постановку задачи: если нам не нужны обороты за период, а требуется исключительно итоговый срез на определенный момент времени, использование таблицы ОстаткиИОбороты является архитектурной ошибкой. Правильнее обратиться к специализированной таблице Остатки.
Таблица остатков сразу возвращает одну запись по каждой уникальной комбинации измерений и субконто, избавляя от необходимости фильтровать дубли по датам:
ВЫБРАТЬ
ХозрасчетныйОстатки.Субконто1 КАК Документ,
ХозрасчетныйОстатки.СуммаОстаток КАК Остаток
ИЗ
РегистрБухгалтерии.Хозрасчетный.Остатки(&ПериодГраница, Счет = &Счет, , ) КАК ХозрасчетныйОстатки
Чтобы в срез вошли проводки документа, границу времени в коде встроенного языка 1С задают с помощью специального объекта Граница:
ПараметрГраница = Новый Граница(КонечнаяДата, ВидГраницы.Включая);
Запрос.УстановитьПараметр("ПериодГраница", ПараметрГраница);
При реализации любого из предложенных вариантов учитывайте следующие правила:
Остатки вместо ОстаткиИОбороты.ИНДЕКСИРОВАТЬ ПО для полей, по которым в дальнейшем будет производиться связь.НОМЕР_СТРОКИ() OVER (...) — это делает код компактным и повышает быстродействие отчета.