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