При формировании аналитических отчетов и выборок часто возникает задача сопоставить объем поступившего товара по документам поступления с актуальным остатком на складе на момент каждого конкретного поступления. Разберем, почему простой запрос к виртуальной таблице остатков не работает для списка дат, с какими проблемами сталкиваются разработчики при построении интервалов и какие существуют оптимальные способы решения этой задачи.
Рассмотрим типовую ошибку при попытке получить остаток на дату каждого документа. Начинающие разработчики часто пытаются соединить табличную часть документа с виртуальной таблицей РегистрНакопления.ТоварыНаСкладах.Остатки:
ВЫБРАТЬ
ПоступлениеТовары.Ссылка.Номер КАК Номер,
ПоступлениеТовары.Ссылка.Дата КАК Дата,
ПоступлениеТовары.Количество КАК Количество,
ТоварыОстатки.КоличествоОстаток КАК КоличествоОстаток
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТовары
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК ТоварыОстатки
ПО ПоступлениеТовары.Номенклатура = ТоварыОстатки.Номенклатура
И ПоступлениеТовары.Ссылка.Склад = ТоварыОстатки.Склад
В параметрах виртуальных таблиц языка запросов 1С (первый параметр — Период) разрешено использовать только константы или параметры запроса (например, &Период). Передать туда поле даты из другой таблицы запроса платформа не позволяет. Если оставить параметр периода пустым, виртуальная таблица вернет текущий актуальный остаток на момент выполнения запроса для всех строк, а не срез на дату каждого документа.
Классический подход из базы знаний заключается в формировании непрерывных интервалов дат, на протяжении которых остаток товара не менялся. Для этого используется виртуальная таблица ОстаткиИОбороты с периодичностью День, соединенная сама с собой по условию нестрогого неравенства дат.
Разберем структуру такого запроса:
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ОстаткиИОбороты1.Период, ДЕНЬ) КАК ДатаС,
МИНИМУМ(ВЫБОР
КОГДА ОстаткиИОбороты2.Период ЕСТЬ NULL
ТОГДА НАЧАЛОПЕРИОДА(&НаДату, ДЕНЬ)
ИНАЧЕ НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(ОстаткиИОбороты2.Период, ДЕНЬ, -1), ДЕНЬ)
КОНЕЦ) КАК ДатаПо,
ОстаткиИОбороты1.КоличествоКонечныйОстаток КАК КоличествоОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
,
,
День,
,
Номенклатура = &Номенклатура
И Склад = &Склад) КАК ОстаткиИОбороты1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
,
,
День,
,
Номенклатура = &Номенклатура
И Склад = &Склад) КАК ОстаткиИОбороты2
ПО ОстаткиИОбороты1.Период < ОстаткиИОбороты2.Период
СГРУППИРОВАТЬ ПО
ОстаткиИОбороты1.КоличествоКонечныйОстаток,
НАЧАЛОПЕРИОДА(ОстаткиИОбороты1.Период, ДЕНЬ)
После получения таблицы интервалов мы можем соединить ее с документами по условию вхождения даты поступления в диапазон ПоступлениеТовары.Ссылка.Дата МЕЖДУ Интервалы.ДатаС И Интервалы.ДатаПо.
Ограничения данного подхода:
День объединяет все движения за сутки. Если в один день было несколько поступлений или отгрузок, остаток на срез дня не покажет точное состояние склада на секунду проведения каждого документа.ОстаткиИОбороты1.Период < ОстаткиИОбороты2.Период) создает декартово произведение строк. На больших базах данных с длительной историей это приводит к существенному замедлению работы СУБД.Если документ поступления сам делает движения по регистру остатков, задачу можно решить значительно проще и производительнее. Для этого обратимся к виртуальной таблице ОстаткиИОбороты с периодичностью Авто или Регистратор.
Посмотрим на пример запроса:
ВЫБРАТЬ
ТоварыНаСкладах.Регистратор.Номер КАК НомерДокумента,
ТоварыНаСкладах.Период КАК ДатаДокумента,
ТоварыНаСкладах.КоличествоПриход КАК КоличествоПоступления,
ТоварыНаСкладах.КоличествоНачальныйОстаток КАК ОстатокДоПоступления,
ТоварыНаСкладах.КоличествоКонечныйОстаток КАК ОстатокПослеПоступления
ИЗ
РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(
&ДатаНачала,
&ДатаОкончания,
Авто,
Движения,
Номенклатура = &Номенклатура
И Склад = &Склад) КАК ТоварыНаСкладах
ГДЕ
ТоварыНаСкладах.Регистратор ССЫЛКА Документ.ПоступлениеТоваровУслуг
Преимущество этого метода заключается в том, что платформа рассчитывает начальный остаток (строго до проведения документа) и конечный остаток (с учетом движений документа) автоматически и с точностью до позиции регистратора в последовательности.
Если отчет разрабатывается на базе СКД, механизм компоновки данных умеет самостоятельно рассчитывать остатки на произвольные моменты времени без сложных конструкций в запросе.
Разберем по шагам порядок настройки:
НачальныйОстаток или КонечныйОстаток и привязываем их к соответствующему полю периода.Если требуется получить остаток для одного или нескольких конкретных документов в коде модулей, используем метод менеджера регистра с передачей момента времени или границы:
Отбор = Новый Структура;
Отбор.Вставить("Номенклатура", ВыбраннаяНоменклатура);
Отбор.Вставить("Склад", ВыбранныйСклад);
// Получаем остаток строго до проведения документа
ГраницаОстатка = Новый Граница(ДокументСсылка.МоментВремени(), ВидГраницы.Исключая);
ТаблицаОстатков = РегистрыНакопления.ТоварыНаСкладах.Остатки(ГраницаОстатка, Отбор);
Если ТаблицаОстатков.Количество() > 0 Тогда
ОстатокНаМомент = ТаблицаОстатков[0].Количество;
КонецЕсли;
Использование объекта Граница с параметром ВидГраницы.Исключая позволяет исключить движения самого документа поступления и получить чистый остаток, предшествующий поступлению партии.