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