При работе с виртуальными таблицами регистров бухгалтерии и накопления (например, Хозрасчетный.ОстаткиИОбороты) разработчики часто сталкиваются с ситуацией, когда таблица возвращает только те даты, в которых были реальные движения или проводки. Если в какой-то из дней периода хозяйственных операций не было, этот день просто выпадает из выборки. Рассмотрим подробнее, как получить полную последовательность дат начиная с &НачалоПериода по &КонецПериода и корректно соединить её с данными регистра.
Выясним причину такого поведения платформы. Виртуальная таблица ОстаткиИОбороты оптимизирует работу с базой данных и формирует строки исключительно по существующим записям. Если за конкретную дату движения отсутствуют, система не создает пустые строки с нулевыми оборотами. В результате при построении графиков или отчетов за каждый день месяца в выборке появляются «дыры».
Разберем основные методы, позволяющие гарантированно получить все даты за требуемый интервал.
Если задача решается в рамках Системы компоновки данных (СКД), формировать таблицу дат вручную в запросе не требуется. В СКД предусмотрен встроенный механизм дополнения периодов.
Период и откроем её свойства (двойным кликом мышью).НачалоПериода и КонецПериода).В этом случае платформа автоматически добавит недостающие дни непосредственно при компоновке отчета.
Проанализируем возможность дополнения дат средствами языка запросов. Для этого применяется конструкция ИТОГИ ... ПЕРИОДАМИ.
Посмотрим на пример построения такого запроса:
ВЫБРАТЬ
ХозрасчетныйОстаткиИОбороты.Период КАК Период,
ХозрасчетныйОстаткиИОбороты.Субконто1 КАК Контрагент,
ХозрасчетныйОстаткиИОбороты.СуммаОборотКт КАК СуммаЗайма
ИЗ
РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , Счет = &Счет, , ) КАК ХозрасчетныйОстаткиИОбороты
ИТОГИ
СУММА(СуммаЗайма)
ПО
Период ПЕРИОДАМИ(ДЕНЬ, &НачалоПериода, &КонецПериода)
При выполнении такого запроса платформа создаст итоговые группировки по всем дням интервала. Обратите внимание: при обходе результатов запроса в коде на встроенном языке необходимо использовать третий параметр метода Выбрать():
ВыборкаПериод = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Период", "ВСЕ");
Важно: конструкция ПЕРИОДАМИ работает только в секции итогов на самом верхнем уровне запроса. Ее нельзя использовать во вложенных запросах или помещать результат во временные таблицы для дальнейших соединений.
Если результат с датами нужно передать во временную таблицу или связать с другими данными через ЛЕВОЕ СОЕДИНЕНИЕ, сформируем последовательность чисел с помощью декартова произведения таблиц цифр, а затем прибавим полученные дни к начальной дате функцией ДОБАВИТЬКДАТЕ.
Рассмотрим пошагово данный алгоритм в запросе:
ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ втЦифра
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 2
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 3
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 5
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 6
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 7
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 8
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 9
;
ВЫБРАТЬ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, t100.Цифра * 100 + t10.Цифра * 10 + t1.Цифра) КАК Дата
ПОМЕСТИТЬ втКалендарь
ИЗ
втЦифра КАК t1
ПО ИСТИНА
втЦифра КАК t10
ПО ИСТИНА
втЦифра КАК t100
ГДЕ
ДОБАВИТЬКДАТЕ(&НачалоПериода, ДЕНЬ, t100.Цифра * 100 + t10.Цифра * 10 + t1.Цифра) <= &КонецПериода
;
ВЫБРАТЬ
втКалендарь.Дата КАК Период,
ЕСТЬNULL(ХозрасчетныйОстаткиИОбороты.СуммаОборотКт, 0) КАК СуммаЗайма
ИЗ
втКалендарь КАК втКалендарь
ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , Счет = &Счет, , ) КАК ХозрасчетныйОстаткиИОбороты
ПО втКалендарь.Дата = ХозрасчетныйОстаткиИОбороты.Период
УПОРЯДОЧИТЬ ПО
Период
В этом примере мы сгенерировали последовательность из 1000 чисел (от 0 до 999), с помощью функции ДОБАВИТЬКДАТЕ прибавили дни к &НачалоПериода и установили условие отбора по &КонецПериода. Полученную временную таблицу втКалендарь соединили с данными регистра бухгалтерии.
Обратите внимание на использование функции ЕСТЬNULL. Для дней, в которых проводки отсутствовали, левое соединение вернет значение NULL, поэтому его необходимо заменять на нуль для корректного отображения финансовых показателей.
Разберем еще один практичный подход, при котором календарь дат формируется на встроенном языке 1С и передается в запрос в качестве параметра.
Сначала в коде создается ТаблицаЗначений и заполняется днями периода:
ТаблицаДаты = Новый ТаблицаЗначений;
ТаблицаДаты.Колонки.Добавить("Дата", Новый ОписаниеТипов("Дата"));
ТекущаяДатаПериода = НачалоДня(НачалоПериода);
Пока ТекущаяДатаПериода <= НачалоДня(КонецПериода) Цикл
НоваяСтрока = ТаблицаДаты.Добавить();
НоваяСтрока.Дата = ТекущаяДатаПериода;
ТекущаяДатаПериода = ТекущаяДатаПериода + 86400; // Добавляем 1 день (86400 секунд)
КонецЦикла;
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| втДаты.Дата КАК Период,
| ЕСТЬNULL(Хозрасчетный.СуммаОборотКт, 0) КАК СуммаЗайма
|ИЗ
| &ТаблицаДаты КАК втДаты
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , Счет = &Счет, , ) КАК Хозрасчетный
| ПО втДаты.Дата = Хозрасчетный.Период
|УПОРЯДОЧИТЬ ПО
| Период";
Запрос.УстановитьПараметр("ТаблицаДаты", ТаблицаДаты);
Запрос.УстановитьПараметр("НачалоПериода", НачалоПериода);
Запрос.УстановитьПараметр("КонецПериода", КонецПериода);
Запрос.УстановитьПараметр("Счет", СчетЗайма);
Результат = Запрос.Выполнить();
Данный вариант удобен, когда интервал периода небольшой или когда в конфигурации уже используется заполненный производственный календарь или справочник с рабочими днями.
Выбор подходящего решения зависит от архитектуры задачи: для отчетов СКД наилучшим решением станет настройка дополнения периодов, для логики внутри процедур — генерация таблицы дат непосредственно в запросе или передача готовой таблицы из кода 1С.