Как получить остатки и обороты по дням за период, включая дни без движений в 1С?

← На главную

При построении отчетов по виртуальным таблицам регистров накопления (например, ОстаткиИОбороты) разработчики часто сталкиваются со следующей особенностью: виртуальная таблица с периодичностью День возвращает строки только за те даты, в которых фактически были движения (приход или расход). Если за какой-то день операций не было, эта дата просто отсутствует в результате выборки.

Однако в реальных задачах заказчику требуется видеть полный календарный период «день за днем» — даже если обороты за конкретную дату равны нулю. Проанализируем причину такого поведения и подробно разберем три основных способа решения этой задачи.

Выясним причину проблемы

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

Чтобы вывести все даты указанного интервала, необходимо предварительно сформировать полную последовательность дней выбранного периода (так называемый «календарь») и уже к ней присоединять данные регистра.

Способ 1. Создание таблицы дат во внешнем коде и передача в запрос

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

Программное формирование таблицы значений, содержащей колонку Период со всеми днями интервала.
Передача этой таблицы значений в запрос в качестве параметра и соединение её с виртуальной таблицей регистра с помощью ЛЕВОГО СОЕДИНЕНИЯ.

Разберем готовый пример кода 1С:


НачалоПериода = Отчет.НачалоПериода;
КонецПериода   = Отчет.КонецПериода;

// Сформируем таблицу всех дат за выбранный интервал
ТаблицаПериодов = Новый ТаблицаЗначений;
ТаблицаПериодов.Колонки.Добавить("Период", Новый ОписаниеТипов("Дата"));

ТекущаяДатаПериода = НачалоПериода;
Пока ТекущаяДатаПериода <= КонецПериода Цикл
    НоваяСтрока = ТаблицаПериодов.Добавить();
    НоваяСтрока.Период = ТекущаяДатаПериода;
    ТекущаяДатаПериода = ТекущаяДатаПериода + 86400; // Добавляем 24 часа
КонецЦикла;

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

Запрос.УстановитьПараметр("ТаблицаПериодов", ТаблицаПериодов);
Запрос.УстановитьПараметр("НачалоПериода", НачалоПериода);
Запрос.УстановитьПараметр("КонецПериода", КонецПериода);
Запрос.УстановитьПараметр("Водитель", Отчет.Водитель);

РезультатЗапроса = Запрос.Выполнить().Выгрузить();

Обратим внимание на важную деталь: использование функции ЕСТЬNULL() гарантирует, что для дат без движений вместо значения NULL будут выведены нули.

Способ 2. Генерация календаря внутри запроса без передачи параметров

Если нет возможности или желания подготавливать таблицу значений во внешнем коде, можно сгенерировать последовательность чисел и дат средствами языка запросов. Для этого применяется декартово произведение мелких подзапросов с использованием функции ДОБАВИТЬКДАТЕ.

Посмотрим на пример такого запроса:


ВЫБРАТЬ
	ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d) КАК Период
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
	(ВЫБРАТЬ 0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК aa,
	(ВЫБРАТЬ 0 КАК b ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК bb,
	(ВЫБРАТЬ 0 КАК c ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК cc,
	(ВЫБРАТЬ 0 КАК d ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК dd
ГДЕ
	aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d <= РАЗНОСТЬДАТ(&НачалоПериода, &КонецПериода, ДЕНЬ)
ИМЕЮЩИЕ
	ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, aa.a * 1000 + bb.b * 100 + cc.c * 10 + dd.d) <= &КонецПериода
;

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

Данный подход позволяет выстроить временную таблицу дат длиной до 10 000 дней (более 27 лет) полностью внутри одного запроса без привлечения внешнего кода 1С.

Способ 3. Использование механизма дополнения периодов в СКД

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

Проанализируем шаги настройки дополнения периодов в компоновке данных:

На вкладке «Настройки» схеме компоновки данных переходим в структуру отчета.
Добавляем группировку по полю ПериодДень (или Период).
Открываем свойства этой группировки и находим опцию Тип дополнения.
Устанавливаем значение День.
Поля Начальная дата и Конечная дата связываем с соответствующими параметрами отчета (&НачалоПериода и &КонецПериода).

После этого СКД самостоятельно дополнит результат недостающими датами и отобразит нулевые строки во всех группировках отчета.

Разберем нюансы работы с остатками по дням

При выводе оборотов достаточно использовать простое соединение по равенству дат (ПО ТаблицаДат.Период = Регистр.Период). Однако если вам необходимо выводить не только обороты, но и конечные остатки на каждый день, обычное соединение не подойдет — остаток предыдущего дня перестанет «протягиваться» на последующие дни без движений и превратится в 0.

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


НачальныйОстаток + СУММА(ВЫБОР КОГДА Движения.Период <= ВТ_Календарь.Период ТОГДА Движения.Оборот ИНАЧЕ 0 КОНЕЦ)

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